RDBMS SQL PRACTICAL SET 2. BCA SQL PRACTICAL SET 2.

Hello Everyone This is Gautam, in this post i m going to solve some basic  SQL query Practice Set 2 this is continuous of set 1. if you don't know about set 1 then refer set 1: 

link is given below.

Note:- All the Given Query Solution is Tested in Microsoft SQL Server Management Studio 18 if  you get any wrong query solution then let me know by comments section.

-----------------------------------PRACTICAL LIST 2----------------------------------

-------------------------------------------------------------------------------------

--> 1) Alter salesman table by setting snum as primary key. 

ALTER TABLE SALESMAN ADD PRIMARY KEY(SNUM);

--> 2) Alter customer table by setting cnum as primary key. 

ALTER TABLE CUSTOMERS ADD PRIMARY KEY(CNUM);

--> 3) Alter orders table by setting onum as primary key. 

ALTER TABLE ORDERS ALTER COLUMN ONUM INT NOT NULL;

ALTER TABLE ORDERS ADD PRIMARY KEY(ONUM);

--> 4) Alter orders table by adding snum and cnum as foreign keys 

--> ALTER TABLE ORDERS ADD FOREIGN KEY(CNUM) REFERENCES CUSTOMERS (CNUM);

-->ALTER TABLE ORDERS ADD FOREIGN KEY(SNUM) REFERENCES SALESMAN (SNUM);

--> 5) Alter customer table by adding SNUM as foreign keys 

ALTER TABLE CUSTOMERS ADD CONSTRAINT FK FOREIGN KEY(SNUM) REFERENCES SALESMAN(SNUM);

--> 6) Insert any five records in customers table. 

--As we know 

--Each non-null value you insert into a foreign key column must be equal to some value

--in the corresponding parent key of the parent table.

--If any column in the foreign key is null, the entire foreign key is considered null.

--Step 1 : Insert 5 records in salesman table because that is a parent table or you can insert only CNUM unique values in salesman table 

--after that you will be able to insert into customers tables 

INSERT INTO SALESMAN (SNUM) VALUES(1009);

INSERT INTO SALESMAN (SNUM) VALUES(1010);

INSERT INTO SALESMAN (SNUM) VALUES(1011);

INSERT INTO SALESMAN (SNUM) VALUES(1012);

INSERT INTO SALESMAN (SNUM) VALUES(1013);

--IF YOU WANT DELETE A ROW FROM TABLE THEN EXECUTE THIS QUERY

--DELETE FROM SALESMAN WHERE SNUM=1008;

--SELECT * FROM SALESMAN;

INSERT INTO CUSTOMERS VALUES(2009,'Riya','Punjab',210,1011);

INSERT INTO CUSTOMERS VALUES(2010,'Ram','London',111,1009),(2011,'Payal','Bihar',211,1010);

INSERT INTO CUSTOMERS VALUES(2012,'Lalan','Bihar',212,1012),(2013,'Gautam','Bihar',213,1013);

--Now you can check value inserted or not 

SELECT * FROM CUSTOMERS;

--> 7) Update the name of the customer in the customers table from Lalit to Girish 

UPDATE CUSTOMERS SET CNAME='Girish' WHERE CNAME='Lalit';

-->Now you can check name has been updated Lalit to Girish

SELECT * FROM CUSTOMERS;

--> 8) Remove all orders from customer Chirag from the orders table. 

DELETE FROM ORDERS WHERE CNUM=(SELECT CNUM FROM CUSTOMERS WHERE CNAME='Chirag');

-->Now you can check Chirag has been removed from table

SELECT * FROM CUSTOMERS;

THANKS FOR VISITING THIS BLOG POST 💖