worksheet on MYSQL - 2

WORKSHEET ON MYSQL | CLASS : XII | COMPUTER SCIENCE
Q.1 Consider the following tables: COMPANY
Write the output of SQL commands :-
(i) Select DISTINCT CompHO from Company;
(ii) Select CompName, ContactPerson from Company where Comp_ID > 3 ;
Q.2 Consider the following tables: MODEL.
Write SQL commands for queries:
(i) To display the details of all the models in the Model table in year 2012.
(ii) To display the details of those models whose Cost is below 2000.
(iii) To decrease the cost of all the models in Model table by 15%.
Q.3 Consider the following tables: STOCK

Write SQL statements for the queries
(i) To display details of all the Items in the Stock table.
(ii) To display details of those Items in Stock table whose Dealer Code(Dcode) is 102.
(iii) To display details of those Items in Stock table whose (Qty) is more than 100.
(iv) To insert a record in the Stock table with the values:
(5010, ‘Pencil HB’, 102, 500, 10, ‘2010-01-26’)
(v) Select DISTINCT Dcode from Stock;
(vi) Select Qty * UnitPrice from Stock where ItemNo=5006;
Q.4 Consider the following table CARDEN given below:
Write SQL commands for the following statements:
(i) To display the names of all the silver-coloured cars.
(ii) To display name, make and capacity of cars.
(iii) To display the charges at which a vehicle can be hired from CARDEN.
Give the output of the following SQL queries:

(i) SELECT DISTINCT Make FROM CARDEN;
(ii) SELECT CarName FROM CARDEN WHERE Capacity=4;
Q.5 Write the SQL commands for (a) to (d) and output for (e):
(a) List the names of those students who obtained DIV I.
SELECT * FROM GRADUATE WHERE DIV = ‘I’;
(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend
received in a year assuming that the STIPEND is paid every month.
SELECT NAME , STIPEND,SUBJECT , STIPEND * 12 FROM GRADUATE ;
(c) To insert a new row in the GRADUATE table: 11,“KAJOL”, 300, “computer sc”,
75, 1
INSERT INTO GRADUATE VALUES(11,“KAJOL”, 300, “computer sc”, 75, ‘II’);
Q.6 Write SQL commands for (i) to (vi) and write output for (vii) on the basis of PRODUCTS
relation given below:

(i) To show details of all PCs with stock more than 110.
(ii) To list the company which gives warranty of more than 2 years.
(iii) To show the PRODUCT name of the products which are within warranty as on date.
(vii) Give the output of the following statements:
(a) Select distinct company from PRODUCT;
(b) Select price from PRODUCT where WARRANTY<=3;
Q.7 Write SQL Commands for (i) to (v) and write the outputs for (vi) to (viii) on the basis of
the following table:
(i) To list the details of furniture whose price is more than 10000.
(ii) To delete the record of all items where discount is 30.
DELETE FROM FURNITURE WHERE DISCOUNT=30;
(iii) To display the price of ‘Babycot’.
(iv) Select Distinct Type from Furniture;
Q.8 Consider the table emp:

Dept:
i Increase salary of employee records by 10% (table emp).
Ii Give commission of Rs. 500 to all employees who joined in year 1982 (table Emp).
Iii Allocate the department situated in BOSTON to employee with employee number 7500
(tables EMPL, Dept)
Iv Modify table Emp, add another column called Grade of VARCHAR type, size 1 into it.
v Insert a record of your choice in table Empl. Make sure not to enter Grade.
Q.9 A departmental store MyStore is considering to maintain their inventory using SQL to
store the data. As a database administer, Abhay has decided that :
• Name of the database - mystore
• Name of the table - STORE
• The attributes of STORE are as follows:
ItemNo - numeric
ItemName – character of size 20
Scode - numeric
Quantity – numeric
A Identify the attribute best suitable to be declared as a primary key,
B Write the degree and cardinality of the table STORE.
C Insert the following data into the attributes ItemNo, ItemName and SCode respectively
in the given table STORE.
ItemNo = 2010, ItemName = “Note Book” and Scode =25
D Abhay want to remove the table STORE from the database MyStore. Which command
will he use from the following:
a) DELETE FROM store;

b) DROP TABLE store;
c) DROP DATABASE mystore;
d) DELETE store FROM mystore;
E Now Abhay wants to display the structure of the table STORE, i.e, name of the
attributes and their respective data types that he has used in the table. Write the query
to display the same.
Q.10 Write SQL commands for the following queries (i) to (v) based on the relations Teacher
and Posting given below:
A To show all information about the teacher of History department.
B To list the names of female teachers.
C To list the names of all teachers with their date of joining.
D To display teacher’s name, salary, age for male teachers only.

E To display name, bonus for each teacher where bonus is 10% of salary.
Q.11
a) Create the table Employee based on the following table instance chart.
Drop table Employee and Department.

(b)
c) Create table Customer as per following Table Instance Chart.
Add one column Email of data type VARCHAR and size 30 to the table Customer.
d)
e)
Add one more column CustomerincomeGroup of datatype VARCHAR(10).
f)
Insert few records with relevant information, in the table.
Drop the column CustomerlncomeGroup from table Customer.

g)
Q12 Q. Consider the following MOVIE database and answer the SQL queries based on it.
a) Retrieve movies information without mentioning their column names.

b) List business done by the movies showing only MovieID, MovieName and
BusinessCost.
c) List the different categories of movies.
d) Find the net profit of each movie showing its ID, Name and Net Profit.
(Hint: Net Profit = BusinessCost – ProductionCost)
Make sure that the new column name is labelled as NetProfit. Is this column now a part
of the MOVIE relation. If no, then what name is coined for such columns?
e) List all movies with ProductionCost greater than 80,000.
f) List all movies which fall in the category of Comedy.
g) List the movies which have not been released yet.
h) List all movies which fall in the category of Comedy or Horror.
i) List all movies whose production cost fall in the range 30500 to 100000.

worksheet on MYSQL - 2

Uploaded by

Copyright:

Available Formats

worksheet on MYSQL - 2

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

worksheet on MYSQL - 2

Uploaded by

Copyright:

Available Formats

WORKSHEET ON MYSQL | CLASS : XII | COMPUTER SCIENCE

Q.1 Consider the following tables: COMPANY

Write the output of SQL commands :-

(i) Select DISTINCT CompHO from Company;

(ii) Select CompName, ContactPerson from Company where Comp_ID > 3 ;

Q.2 Consider the following tables: MODEL.

Write SQL commands for queries:

Q.3 Consider the following tables: STOCK

(i) To display details of all the Items in the Stock table.

(iv) To insert a record in the Stock table with the values:

(5010, ‘Pencil HB’, 102, 500, 10, ‘2010-01-26’)

(v) Select DISTINCT Dcode from Stock;

(vi) Select Qty * UnitPrice from Stock where ItemNo=5006;

Q.4 Consider the following table CARDEN given below:

Write SQL commands for the following statements:

(i) To display the names of all the silver-coloured cars.

(ii) To display name, make and capacity of cars.

Give the output of the following SQL queries:

(ii) SELECT CarName FROM CARDEN WHERE Capacity=4;

(a) List the names of those students who obtained DIV I.

SELECT * FROM GRADUATE WHERE DIV = ‘I’;

received in a year assuming that the STIPEND is paid every month.

SELECT NAME , STIPEND,SUBJECT , STIPEND * 12 FROM GRADUATE ;

INSERT INTO GRADUATE VALUES(11,“KAJOL”, 300, “computer sc”, 75, ‘II’);

relation given below:

(vii) Give the output of the following statements:

(a) Select distinct company from PRODUCT;

(b) Select price from PRODUCT where WARRANTY<=3;

the following table:

(ii) To delete the record of all items where discount is 30.

DELETE FROM FURNITURE WHERE DISCOUNT=30;

(iii) To display the price of ‘Babycot’.

(iv) Select Distinct Type from Furniture;

Q.8 Consider the table emp:

i Increase salary of employee records by 10% (table emp).

B Write the degree and cardinality of the table STORE.

a) DELETE FROM store;

A To show all information about the teacher of History department.

B To list the names of female teachers.

C To list the names of all teachers with their date of joining.

D To display teacher’s name, salary, age for male teachers only.

Drop table Employee and Department.

c) Create table Customer as per following Table Instance Chart.

Drop the column CustomerlncomeGroup from table Customer.

a) Retrieve movies information without mentioning their column names.

c) List the different categories of movies.

(Hint: Net Profit = BusinessCost – ProductionCost)

e) List all movies with ProductionCost greater than 80,000.

f) List all movies which fall in the category of Comedy.

g) List the movies which have not been released yet.

h) List all movies which fall in the category of Comedy or Horror.

You might also like