SQL COMMANDS | PDF | Data Model | Sql
Scribd
Upload
3 views

SQL COMMANDS

The document contains SQL commands for various database operations across multiple tables including PRODUCT, SENDER, RECIPIENT, STATIONERY, BOOK, GAMES, WATCH, EVENT, COMPANY, SCHOOL, and EMPLOYEE. It provides specific queries for displaying, updating, deleting, and creating records based on various conditions. Each section is organized by table and includes multiple tasks with corresponding SQL statements.

Uploaded by

TVIS Juniors
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
3 views

SQL COMMANDS

The document contains SQL commands for various database operations across multiple tables including PRODUCT, SENDER, RECIPIENT, STATIONERY, BOOK, GAMES, WATCH, EVENT, COMPANY, SCHOOL, and EMPLOYEE. It provides specific queries for displaying, updating, deleting, and creating records based on various conditions. Each section is organized by table and includes multiple tasks with corresponding SQL statements.

Uploaded by

TVIS Juniors
Copyright
© © All Rights Reserved
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 7

SQL COMMANDS

TABLE 1
Write SQL queries for (a) to (d) using the PRODUCT table details given below

PRODUCT

(a) Write the query to display details of all the product whose quantity must be greater
than 100.
SELECT * FROM PRODUCT WHERE QUANTITY>100;
(b) Write the query to increase the price of all the products by 5%;
UPDATE PRODUCT SET PRICE=PRICE+PRICE*5/100;
(c) Write the query to display details of the product where the Product name starts with
‘P’.
SELECT * FROM PRODUCT WHERE PNAME LIKE ‘P%’;
(d) Write the query to delete the record whose company name is ‘TOSHIBA’.
DELETE FROM PRODUCT WHERE COMPANY=’TOSHIBA’;

TABLE 2
2. Consider the following tables SENDER and RECIPIENT, Write SQL queries for
(a) to (d) using the SENDER and RECIPIENT table details given below .

SENDER

RECIPIENT
a) To display the names of all senders from Mumbai.
SELECT SENDERNAME FROM SENDER WHERE SENDERCITY=’MUMBAI’;
b) To display the recID, senderName, senderAddress, RecName, RecAddress
for every recipient.
SELECT RECID,SENDERNAME,SENDERADDRESS,RECNAME,
RECADDRESS FROM SENDER,RECIPIENT WHERE
SENDER.SENDERID=RECIPIENT.SENDERID;
c) List the sender name in alphabetical order.
SELECT SENDERNAME FROM SENDER ORDER BY SENDERNAME ASC;
d) To display reccity and the number of recipients from each city.
SELECT RECCITY,COUNT(RECID) FROM RECIPIENTS GROUB BY RECCITY;

TABLE 3
STATIONERY

a) Write the query to display product name, brand whose stock falls below 200
SELECT PNAME,BRAND FROM STATIONERY WHERE QTY <200;
b) Write the query to display the details of varieties of pens available in the
stationery STATIONERY
SELECT PNAME FROM STATIONERY WHERE PNAME LIKE ‘%PEN%;
c) Write the query to update the quantity of V7 pen to 175.
UPDATE STATIONERY SET QTY=’V7’ WHERE BRAND=’V7’;
d) Write the query to display the number of products purchased from each
brand.

SELECT COUNT(PID),PNAME FROM STATIONERY GROUP BY BRAND;

TABLE 4
BOOK

ISSUED

a)To show Book name, Author name and Price of books of EPB publisher.
SELECT BOOKNAME,AUTHOR,PRICE FROM BOOK WHRE
PUBLISHER=’EPB’;
b) To increase the price of all books of First PubL.by 50.
UPDATE BOOK SET PRICE=PRICE+50 WHERE PUBLISHER=’FIRST PUBL’;
c) To display the Book_ID, Book_Name and Quantity Issued for all books which
have been issued.
SELECT BOOKID,BOOKNAME,QUANTITYISSUED FROM BOOK,ISSUED
WHERE BOOK.BOOKID=ISSUED.BOOKID;
d) To display the details of book which is of type ‘Fiction’.
SELECT * FROM BOOK WHERE TYPE=’FICTION’;

TABLE 5

GAMES
PLAYER

a) To display the name of all Games with their GCodes.


SELECT GAMENAME,GCODE FROM GAMES;
b) To display details of those games which are having PrizeMoney more than
7000.
SELECT GAMENAME FROM GAMES WHERE PRIZEMONEY>7000;
c) To display the content of the GAMES table in ascending order of ScheduleDate.
SELECT * FROM GAMES ORDER BY SCHEDULEDATE ASC;
d) To display the total prize money for all the games.
SELECT SUM(PRIZEMONEY) FROM GAMES;

TABLE 6
WATCH

(a) Write the query to create a database ‘TICKTOCK’


CREATE DATABASE TICKTOCK;

(b) Write the query to display the names of the watches which
is starting with alphabet ‘R’.
SELECT WNAME FROM WATCH WHERE WNAME LIKE ‘R%’;

(c) Write the query to create the above table

CREATE TABLE WATCH(WID INT NOT NULL PRIMARY KEY,WNAME


VARCHAR(20),PRICE INT,TYPE VARCHAR(20),QUANTITY INT);
D) Write the query to change the column QUANTITY to QTY
ALTER TABLE WATCH CHANGE QUANTITY QTY INT;

TABLE 7
2. Consider the following tables EVENT and COMPANY. Write SQL queries for (a)
to (d) using the EVENT and COMPANY table details given below.
COMPANY

a) To display the minimum and maximum date from event table.


SELECT MIN(DATE),MAX(DATE) FROM EVENT;
b)To display EventName, organizer,name from the table Event, company where budget
should be greater than 20000.
SELECT EVENTNAME,ORGANIZER,NAME FROM EVENT,COMPANY WHERE
EVENT.ORGANIZER=ORGANIZER.ORGANIZERID;
c) To display Name from the table Company whose name ends with ‘d’.
SELECT NAME FROM COMPANY WHERE NAME LIKE ‘%D’;
d) To display the budget in ascending order.
SELECT BUDGET FROM EVENT ORDER BY BUDGET ASC;

TABLE 8
PRODUCT

a) Write the query to display all details of the company ‘SONY’ .


SELECT * FROM PRODUCT WHERE COMPANY=’SONY’;
b) Write the query to display pname, quantity, price of the product where price must be
in the range 5000 to 10000.
SELECT PNAME,QUANTITY,PRICE FROM PRODUCT WHERE PRICE BETWEEN
5000 AND 10000;
c) Write the query to create the above table.

CREATE TABLE PRODUCT(PID INT NOT NULL PRIMARY KEY,PNAME VARCHAR(20),


QUANTITY INT, PRICE INT,COMPANY VARCHAR(20));
D) Write the query to change the column company to brand
ALTER TABLE PRODUCT CHANGE COMPANY BRAND VARCHAR(20);

TABLE 9
SCHOOL

a) Write a query to display the name of the teacher with their


experience with ‘PHYSICS’ subject
SELECT TEACHER,EXPERIENCE FROM SCHOOL WHERE SUBJECT=’PHYSICS’;
b) Write a query to display number of teachers with each subject.
SELECT SUBJECT,COUNT(*) FROM SCHOOL GROUP BY SUBJECT;
c) Write a query to display the teacher name, handling subject and their teaching hours
whose experience exceeds 10 years
SELECT TEACHER,SUBJECT,PERIODS FROM SCHOOL WHERE EXPERIENCE >10;
D) Write a query to display the names of the teachers who have joined the school before
APRIL 2000.
SELECT TECAHER FROM SCHOOL WHERE DOJ<’2000-04-01’;

TABLE 10
EMPLOYEE

a) Write the query to remove details of employee, “RAM GUPTA”


who has resigned from the organization.
DELETE FROM EMPLOYEE WHERE TNAME=’RAM GUPTA’;

b) Write the query to update the salary of those employees by 2000 who have joined in
the year 2009.
UPDATE EMPLOYEE SET SALARY=SALARY+2000 WHERE JOINDATE BETWEEN

‘2009-01-01’ AND ‘2009-12-31’;

c) Write the query to add information about a new joinee with details like Tcode as 65,
Tname as ‘ADITHYA’, Depcde as 119 and age as 35 with a salary as 65000 joindate as
NULL

INSERT INTO EMPLOYEE VALUES(65,’ADITHYA’,119,35,65000,NULL);

d) Write the query to display names of employees based on the order of their
experience from highest to lowest.

SELECT TNAME FROM EMPLOYEE ORDER BY JOINDATE DESC;

You might also like