Database Development and Design Week 7: Lab 6 Walkthrough

Lab 6 instructions: (Copy and paste in your SQL Server)

use G0022211111;

–1 run the follow query. How many records are in the table?

Select *
from Cust_Orders;

–2 run this query below. It will enter 3 new records into your table

insert into Cust_Orders
values (1500, 908132, 665, ‘Cash’, ‘2018-03-08 00:00:00’, ‘Electronics’),
(1501, 876661, 128, ‘Credit Card’, ‘2016-05-18 00:00:00’, ‘Toys’),
(1502, 732027, 785, ‘Check’, ‘2018-03-08 00:00:00’, ‘Furniture’)

–3 run the follow query. How many records are in the table this time?

Select *
from Cust_Orders;

–4 Run the following queries. What happened to Order_ID 1502
begin transaction;

delete from Cust_Orders
where Order_ID = ‘1502’;

select *
from Cust_Orders;

–5 Run the following queries. Now what happened to Order_ID 1502
Rollback;

Select *
from Cust_Orders;

–6 Run question 4 again, this time add a commit statement.
–What happens to Order_ID 1502 now

–7 Try using Rollback. Can you undo the delete statement?

–8 Using question 2 as a guide, insert a new record, order_id =1600
— Run a Select * query to see if the record is in your table

–9 Adding a Begin Transaction statement, insert another row
— This time Order_ID 1601. Run Select * to confirm

–10 Try either a commit or rollback command – What happened?

–11 — Turn the query below into a View
Select s.Stud_NM as [Student Name], s.Start_dt as [Start Date], T.Subject,
t1.name as [Tutor Name]
from Student as s
join Tutoring as t
on s.Stud_ID = t.Student_ID
join tutor as t1
on t.Tutor_ID = t1.Tutor_ID

–12 — Run a query from the view you just created where
——-Subject does not start with the letter G

–Bonus Question—-
–13 — Run a query from the view you just created where
——-tutor name does not end with the letter b

Here are the Excel files needed to create the tables:

Cust-OrdersDownload

Link to lesson with video for uploading Excel files into SQL Server:

Leave a Reply