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:
Link to lesson with video for uploading Excel files into SQL Server: