SQL Queries

Download as pdf or txt
Download as pdf or txt
You are on page 1of 6

1(b)Consider the tables BOOKS and ISSUED.

Write SQL commands for given statements: 4


Table: BOOKS
BID BNAME AUNAME PRICE TYPE QTY
COMPl1 LET US C YASHWANT 350 COMPUTER 15
GEOG33 INDIA MAP RANJEET P 150 GEOGRAPHY 20

HIST66 HISTORY RBALA 210 HISTORY 25


COMP12 MY FIRST C VINOD DUA 330 COMPUTER 18

LITR88 MY DREAMS ARVIND AD 470 NOBEL 24

Table: ISSUED
BID QTY ISSUED
HIST66 10

COMPI1
LITR88 15

() Write command to add primary key and foreign key in tables using alter.
(üi) Increase the price of all computer books by 70.
(iii) Display the book id, book name and quantity issued for all books which have been issued.
(iv) Display the book id, book name, author name, quantity issued for all books which price are
200 to 400.
1(b) Consider the tables

Table : cUSTOMERS Table : PURCHASES


CNO CNAME CITIES SNO QTY PUR DATE CNO
C1 SANYAM DELHI S1 15 2018-12-25 C2
C2 SHRUTI DELHI S2 10 2018-11-10 C1
C3 MEHER MUMBAI S3 12 2018-11-10 C4
C4 SAKSHI CHENNAI S4 7 2019-01-12 C7
C5 RITESH INDORE S5 11 2019-02-12 C2
C6 RAHUL DELHI S6 10 2018-10-12 C6
C7 AMEER CHENNAI ST 5 2019-05-09 C8
c8 MINAKSHI BANGALORE S8 20 2019-05-09 C3
C9 ANSHUL MUMBAI S9 2018-05-09 C9
S10 15 2018-11-12 C5
S11 6 2018-08-04 C7

Write SQL queries for (i) to (iv), which are based on the tables :CUSTOMERS and
PURCHASES given in the question
(i) To display details of all CUSTOMERS whose CITIES are neither Delhi nor Mumbai.
(ü) To display the CNAME and CITIES of all CUSTOMERS in ascending order of their CNAME.
(ii) To display the number of CUSTOMERS along with their respective CITIES in each of the
CITIES.
(iv) To display details of all PURCHASES whose Quantity is more than 15.
1(b) Consider the tables PRODUCT and BRAND given below:
Table: PRODUCT
PCode PName UPrice Rating BID

PO1 Shampoo 120 6 MO3


P02 Toothpaste 54 8 MO2

PO3 Soap 25 7 MO3


P04 Toothpaste 65 4 M04
POS Soap 38 MOS
PO6 Shampoo 245 6 MOS

Table: BRAND

BID BNamne
MO2 Dant Kanti
MO3 Medimnix
MO4 Pepsodent
MOS Dove

Write SQL queries for the following:


(i) Display product name and brand name from the tables PRODUCT and BRAND.
(ii) Display the structure of the table PRODUCT.
(üüi) Display the average rating of Medimix and Dove brands
(iv) Display the name, price, and rating of products in descending order of rating.
1(b) Write following SQL queries based on the tables EMPLOYEE and DEPARTMENT given
below: 4

Table: EMPLOYEE
EMPID NAME DOB DEPTID DESIG SALARY
120 Alisha 23 DO01 Manager 750O0
Jan
1978
123 Nitin 10 DO02 AO 5900o
Oct
1977
129 Navjot 12 DO03 Supervisor 400oo
Jul
1971
130 Jimmy 30 DO0 4 Sales Rep
Dec
1980
131 Faiz O6 DO01 Dep 65000
Apr Manager
1984

Table: DEPARTMENT

DEPTID DEPTNAME FLOORNO


DOO1 PerSonal 4
DO02 Admin 10
DOO3 Production
DOO 4 Sales 3

(i) To display the average salary of all employees, department wise.


(ii) To display name and respective department name of each employee whose salary is more
than 50000.
(iii) To display the names of employees whose salary is not known, in alphabetical order.
(iv) To display DEPTID from the table EMPLOYEE without repetition.
1(b) Consider the tables SHOP and ACCESSORIES:
Table:SHOP
SName Area
SO01 ABC computronics CP
SO02 All Infotech Media GK II
SO03 Tech Shop CP
SO04 Geeks Tecno Soft Nehru Place
SO0S Hitech Tech Store Nehru Place

Table: ACCESSORIES
No Name Price Id
A01 Mother Board 12000 S01
A02 Hard Disk S000 SO1
A03 Keyboard 500 S02
A04 Mouse 300 S01
A05 Mother Board 13000 S02
A06 Keyboard 400 S03
A07 LCD 6000 S04
TO8 LCD 5500 SOs
TO9 Mouse 350 SO5
T10 Hard Disk 4500 S03

Write the following SQL queries:


i) To display Name and price of all the Accessories in ascending order of their price.
ii) To display ld and SName of all Shop located in Nehru place.
ii) To display minimum and maximum price of each Name of Accessories.
iv) To display Name, Price of allaccessories and their respective SName where they are
available.
1(b) Consider the tables EMPLOYEE and DEPARTMENT: 4
Table : EMPLOYEE
TCode TNameea DepCde Salary Age JoinDate
15 Sameer Sharmna 123 75000 39 01-Apr-2007
21 Ragvinder K 101 86000 29 11-Nov-2005

34 Rama Gupta 119 52500 43 03-Marr2010


46 CRMenon 103 67000 38 12-Jul-200-4

77 Mohan Kumar 103 63000 55 25-Nov-2000


B1 Rajesh Kumar 119 74500 48 11-Dec-2008
89 SanjeevP 101 92600 54 12-Jan-2009
93 Pragya Jain 123 32000 29 05-Aug-2006

Table: DEPARTMENT

DepCde DepName DepHead


101 ACCOUNTS Rajiv Kumar
103 HR PK Singh
119 IT Yogesh Kumar
123 RESEARCH Ajay Dutta
Write SQL commands for the following:
i) To display all DepName along with the DepCde in descending order of DepCde.
ii) To display the average age of Employees in DepCde as 103.
ii) To display the name of DepHead of the Employee named 'Sanjeev P'.
iv) To display the details of all Employees who has joined before 2007 from EMPLOYEE table.

You might also like