1. Table Empl has same structure as that of table EMPL.

Write a query
statement to insert data from table NewEmpl into EMPL where salary and
comm is more than Rs. 4000.

2. Insert all those records of table Accounts into table Pending where
amt_outstanding is more than 10000.
3. To view all the details (all columns and rows) of the "employee" table the
below query is executed :
4. Give commission of Rs.500 to all employees who joined in year 1982
(table Empl).

Answer

Table Empl

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT

8369 SMITH CLERK 8902 1990-12-18 800 NULL 20

8499 ANYA SALESMAN 8698 1991-02-20 1600 300 30

8521 SETH SALESMAN 8698 1991-02-22 1250 500 30

8566 MAHADEVAN MANAGER 8839 1991-04-02 2985 NULL 20

8654 MOMIN SALESMAN 8698 1991-09-28 1250 1400 30

8698 BINA MANAGER 8839 1991-05-01 2850 NULL 30

1. Write Query statements for following transaction : (Consider tables of

question 12)

1. Increase price of all products by 10%.

2. List the details of all orders whose payment is pending as per increased
price.
5.Increase salary of employee records by 10% (table employee).

Answer

Table employee

ID First_Name Last_Name User_ID Salary

1 Dim Joseph Jdim 5000

2 Jaganath Mishra jnmishra 4000

3 Siddharth Mishra smishra 8000

4 Shankar Giri sgiri 7000

5 Gautam Buddha bgautam 2000

6.Modify table Empl, add another column called Grade of VARCHAR type, size
1 into it.

7.

Add a constraint (NN-Grade) in table Empl that declares column Grade not null.
8.Modify the definition of column Grade. Increase its size to 2.

9.Drop the table Empl.

10.Create the table Department table based on the following table instance chart.

Column Name ID Name

Data Type NUMBER VARCHAR

Length 8 25

11.Populate the table Department with data from table dept. Including only
required columns.

12.Create the table Employee based on the following table instance chart.

Column Name Data Type Length

ID NUMBER 8

First_Name VARCHAR 25

Last_Name VARCHAR 25

Dept_ID NUMBER 8

First_Name VARCHAR 25

Last_Name VARCHAR 25

Dept_ID NUMBER 8

13.Drop table Employee and Department.

14.Create table Customer as per following Table Instance Chart.

Column Name Data Type Length

Cust_ID NUMBER 7

Cust_Name VARCHAR 30

Cust_Address1 VARCHAR 20
Column Name Data Type Length

Cust_Address2 VARCHAR 30

Pincode NUMBER 6

Cust_Phone VARCHAR 10

Cust_Name VARCHAR 30

Cust_Address1 VARCHAR 20

Cust_Address2 VARCHAR 30

Pincode NUMBER 6

Cust_Phone VARCHAR 10

15.Add one column Email of data type VARCHAR and size 30 to the table
Customer.

16.Add one more column CustomerIncomeGroup of datatype VARCHAR(10).

17.Insert few records with relevant information, in the table.

18.Drop the column CustomerIncomeGroup from table Customer.

19.Create table Department as per following Table Instance Chart.

20.View structures of all tables created by you.

21.Create table Employee as per following Table Instance Chart.

Column EmpAddr EmpPhon EmpS

EmpID EmpName DeptID
Name ess e al

Key Type Primary Foreign

Nulls/
NOT NULL
Unique

Departme
Fk Table
nt

Fk Column Dept_ID

VARCHA VARCH NUM VARCH

Datatype NUMBER VARCHAR
R AR BER AR

Length 6 20 30 10 9, 2 2

22.Consider the following MOVIE table and write the SQL queries based on it.
MovieI MovieNam Categor ReleaseDat ProductionC BusinessCos
D e y e ost t

Hindi_Movi
001 Musical 2018-04-23 124500 130000
e

Tamil_Mov
002 Action 2016-05-17 112000 118000
ie

English_Mo
003 Horror 2017-08-06 245000 360000
vie

Bengali_M Adventu
004 2017-01-04 72000 100000
ovie re

Telugu_Mo
005 Action - 100000 -
vie

Punjabi_Mo
006 Comedy - 30500 -
vie

(a) Display all the information from the Movie table.

(b) List business done by the movies showing only MovieID, MovieName and
Total_Earning. Total_Earning to be calculated as the sum of ProductionCost and
BusinessCost.
(c) List the different categories of movies.

(d) Find the net profit of each movie showing its MovieID, MovieName and
NetProfit. Net Profit is to be calculated as the difference between Business Cost
and Production Cost.

(e) List MovieID, MovieName and Cost for all movies with ProductionCost
greater than 10,000 and less than 1,00,000.

(f) List details of all movies which fall in the category of comedy or action.

(g) List details of all movies which have not been released yet.
23.Suppose your school management has decided to conduct cricket matches
between students of Class XI and Class XII. Students of each class are asked to
join any one of the four teams – Team Titan, Team Rockers, Team Magnet and
Team Hurricane. During summer vacations, various matches will be conducted
between these teams. Help your sports teacher to do the following:

(a) Create a database "Sports".

(b) Create a table "TEAM" with following considerations:

1. It should have a column TeamID for storing an integer value between 1 to

9, which refers to unique identification of a team.
2. Each TeamID should have its associated name (TeamName), which should
be a string of length not less than 10 characters.

(c) Using table level constraint, make TeamID as the primary key.

(d) Show the structure of the table TEAM using a SQL statement.

(e) As per the preferences of the students four teams were formed as given below.
Insert these four rows in TEAM table:

Row 1: (1, Team Titan)

Row 2: (2, Team Rockers)
Row 3: (3, Team Magnet)
Row 4: (4, Team Hurricane)

(f) Show the contents of the table TEAM using a DML statement.

(g) Now create another table MATCH_DETAILS and insert data as shown
below. Choose appropriate data types and constraints for each attribute.

Table: MATCH_DETAILS

FirstTea SecondTeamI FirstTeamSc SecondTeamSc

MatchID MatchDate
mID D ore ore

M1 2018-07-17 1 2 90 86
FirstTea SecondTeamI FirstTeamSc SecondTeamSc
MatchID MatchDate
mID D ore ore

M2 2018-07-18 3 4 45 48

M3 2018-07-19 1 3 78 56

M4 2018-07-19 2 4 56 67

M5 2018-07-18 1 4 32 87

M6 2018-07-17 2 3 67 51

24.Using the sports database containing two relations (TEAM,

MATCH_DETAILS) and write the queries for the following:

(a) Display the MatchID of all those matches where both the teams have scored
more than 70.

(b) Display the MatchID of all those matches where FirstTeam has scored less
than 70 but SecondTeam has scored more than 70.

(c) Display the MatchID and date of matches played by Team 1 and won by it.

(d) Display the MatchID of matches played by Team 2 and not won by it.

(e) Change the name of the relation TEAM to T_DATA. Also change the
attributes TeamID and TeamName to T_ID and T_NAME respectively.

25.A shop called Wonderful Garments who sells school uniforms maintains a
database SCHOOLUNIFORM as shown below. It consisted of two relations -
UNIFORM and COST. They made UniformCode as the primary key for
UNIFORM relations. Further, they used UniformCode and Size to be composite
keys for COST relation. By analysing the database schema and database state,
specify SQL queries to rectify the following anomalies.

(a) M/S Wonderful Garments also keeps handkerchiefs of red colour, medium
size of Rs. 100 each.
(b) INSERT INTO COST (UCode, Size, Price) values (7, 'M', 100);

When the above query is used to insert data, the values for the handkerchief
without entering its details in the UNIFORM relation is entered. Make a
provision so that the data can be entered in the COST table only if it is already
there in the UNIFORM table.

(c) Further, they should be able to assign a new UCode to an item only if it has a
valid UName. Write a query to add appropriate constraints to the
SCHOOLUNIFORM database.

(d) Add the constraint so that the price of an item is always greater than zero.

26.Consider the following table named "Product", showing details of products

being sold in a grocery shop.

PCode PName UPrice Manufacturer

P01 Washing Powder 120 Surf

P02 Toothpaste 54 Colgate

P03 Soap 25 Lux

P04 Toothpaste 65 Pepsodent

P05 Soap 38 Dove

P06 Shampoo 245 Dove

Write SQL queries for the following:

(a) Create the table Product with appropriate data types and constraints.

(b) Identify the primary key in Product.

(c) List the Product Code, Product name and price in descending order of their
product name. If PName is the same, then display the data in ascending order of
price.

(d) Add a new column Discount to the table Product.

(e) Calculate the value of the discount in the table Product as 10 per cent of the
UPrice for all those products where the UPrice is more than 100, otherwise the
discount will be 0.

(f) Increase the price by 12 per cent for all the products manufactured by Dove.

(g) Display the total number of products manufactured by each manufacturer.

Write the output(s) produced by executing the following queries on the basis of
the information given above in the table Product:

(h) SELECT PName, avg(UPrice) FROM Product GROUP BY Pname;

(i) SELECT DISTINCT Manufacturer FROM Product;

(j) SELECT COUNT (DISTINCT PName) FROM Product;

(k) SELECT PName, MAX(UPrice), MIN(UPrice) FROM Product GROUP BY

PName;

27.Using the CARSHOWROOM database given in the chapter, write the SQL
queries for the following:

(a) Add a new column Discount in the INVENTORY table.

(b) Set appropriate discount values for all cars keeping in mind the following:

1. No discount is available on the LXI model.

2. VXI model gives a 10 per cent discount.
3. A 12 per cent discount is given on cars other than LXI model and VXI
model.

(c) Display the name of the costliest car with fuel type "Petrol".

(d) Calculate the average discount and total discount available on Baleno cars.
(e) List the total number of cars having no discount.
Table inventory

CarId CarName Price Model YearManufacture FuelType

D001 Dzire 582613.00 LXI 2017 Petrol

D002 Dzire 673112.00 VXI 2018 Petrol

B001 Baleno 567031.00 Sigma1.2 2019 Petrol

B002 Baleno 647858.00 Delta1.2 2018 Petrol

E001 EECO 355205.00 5 STR STD 2017 CNG

E002 EECO 654914.00 CARE 2018 CNG

S001 SWIFT 514000.00 LXI 2017 Petrol

S002 SWIFT 614000.00 VXI 2018 Petrol