Grade 12 CS SQL Practice:
Question 1:
Meenu has been entrusted with the management of NSE Database. She needs to access some
information from STOCK and TRADERS tables for a survey analysis. Help him extract the following
information by writing the desired SQL queries as mentioned below.
CREATE TABLE TRADERS (
TCODE VARCHAR(5) PRIMARY KEY,
TNAME VARCHAR(30),
CITY VARCHAR(20)
);
INSERT INTO TRADERS VALUES ('T01', 'RELIANCE DIGITAL', 'MUMBAI');
INSERT INTO TRADERS VALUES ('T02', 'TATA DIGITAL', 'BHUBANESWAR');
INSERT INTO TRADERS VALUES ('T03', 'BIRLA DIGITAL', 'NEW DELHI');
CREATE TABLE STOCK (
SCODE INT PRIMARY KEY,
SNAME VARCHAR(30),
QTY INT,
PRICE INT,
BRAND VARCHAR(20),
TCODE VARCHAR(5),
FOREIGN KEY (TCODE) REFERENCES TRADERS(TCODE)
);
INSERT INTO STOCK VALUES (1001, 'COMPUTER', 90, 45000, 'DELL', 'T01');
INSERT INTO STOCK VALUES (1006, 'LCD PROJECTOR', 40, 42000, 'NEC', 'T02');
INSERT INTO STOCK VALUES (1004, 'IPAD', 100, 55000, 'APPLE', 'T01');
INSERT INTO STOCK VALUES (1003, 'DIGITAL CAMERA', 160, 15000, 'SAMSUNG', 'T02');
INSERT INTO STOCK VALUES (1005, 'LAPTOP', 600, 35000, 'HP', 'T03');
Write SQL queries for the following:
i) Display the SNAME, QTY, PRICE, TCODE, and TNAME of all the stocks in the STOCK and
TRADERS tables.
ii) Display the details of all the stocks with a price >= 35000 and <=50000 (inclusive).
iii) Display the SCODE, SNAME, QTY*PRICE as the “TOTAL PRICE” of BRAND “NEC” or “HP” in
ascending order of QTY*PRICE.
iv) Display the number of stock items in each TCODE.
SELECT [Link], [Link], [Link], [Link], [Link]
FROM STOCK S, TRADERS T
WHERE [Link] = [Link];
SELECT *
FROM STOCK
WHERE PRICE BETWEEN 35000 AND 50000;
SELECT SCODE, SNAME, QTY*PRICE AS "TOTAL PRICE"
FROM STOCK
WHERE BRAND IN ('NEC', 'HP')
ORDER BY QTY*PRICE ASC;
SELECT TCODE, COUNT(*) AS "NUMBER OF ITEMS"
FROM STOCK
GROUP BY TCODE;
Question 2:
Consider the following tables employees, empsalary.
CREATE TABLE EMPLOYEES (
Empid INT PRIMARY KEY,
Firstname VARCHAR(20),
Lastname VARCHAR(20),
Address VARCHAR(30),
City VARCHAR(20)
);
INSERT INTO EMPLOYEES VALUES (10, 'Ravi', 'Kumar', 'Raj nagar', 'GZB');
INSERT INTO EMPLOYEES VALUES (105, 'Harry', 'Waltor', 'Gandhi nagar', 'GZB');
INSERT INTO EMPLOYEES VALUES (152, 'Sam', 'Tones', '33 Elm St.', 'Paris');
INSERT INTO EMPLOYEES VALUES (215, 'Sarah', 'Ackerman', '440 U.S. 110', 'Upton');
INSERT INTO EMPLOYEES VALUES (244, 'Manila', 'Sengupta', '24 Friends street', 'New Delhi');
INSERT INTO EMPLOYEES VALUES (300, 'Robert', 'Samuel', '9 Fifth Cross', 'Washington');
INSERT INTO EMPLOYEES VALUES (335, 'Ritu', 'Tondon', 'Shastri Nagar', 'GZB');
INSERT INTO EMPLOYEES VALUES (400, 'Rachel', 'Lee', '121 Harrison St.', 'New York');
INSERT INTO EMPLOYEES VALUES (441, 'Peter', 'Thompson', '11 Red Road', 'Paris');
CREATE TABLE EmpSalary (
Empid INT,
Salary INT,
Benefits INT,
Designation VARCHAR(20),
FOREIGN KEY (Empid) REFERENCES EMPLOYEES(Empid)
);
INSERT INTO EmpSalary VALUES (10, 75000, 15000, 'Manager');
INSERT INTO EmpSalary VALUES (105, 65000, 15000, 'Manager');
INSERT INTO EmpSalary VALUES (152, 80000, 25000, 'Director');
INSERT INTO EmpSalary VALUES (215, 75000, 12500, 'Manager');
INSERT INTO EmpSalary VALUES (244, 50000, 12000, 'Clerk');
INSERT INTO EmpSalary VALUES (300, 45000, 10000, 'Clerk');
INSERT INTO EmpSalary VALUES (335, 40000, 10000, 'Clerk');
INSERT INTO EmpSalary VALUES (400, 32000, 7500, 'Salesman');
INSERT INTO EmpSalary VALUES (441, 28000, 7500, 'Salesman');
Write the SQL commands for the following:
a) To show first name, last name, address and city of all employees who lives in Paris.
b) To display the details of Employees table in descending order of First name.
c) To display the first name, last name and salary of all employees from the tables Employee
and EmpSalary, who are working as Manager.
d) Display the name of the employees whose benefits are less than 10000.
SELECT Firstname, Lastname, Address, City
FROM EMPLOYEES
WHERE City = 'Paris';
SELECT *
FROM EMPLOYEES
ORDER BY Firstname DESC;
SELECT [Link], [Link], [Link]
FROM EMPLOYEES E, EmpSalary S
WHERE [Link] = [Link]
AND [Link] = 'Manager';
SELECT [Link], [Link]
FROM EMPLOYEES E, EmpSalary S
WHERE [Link] = [Link]
AND [Link] < 10000;