0
[Link] Topic [Link].
1 Acknowledgement 1
2 Certificate 2
3 Q.1 Create database RYAN. Create table 3-12
STUDENT. Create table ACTIVITY
4 Q.2 EMP and DEPT tables 13-15
5 Q.3 BOOKS and ISSUED tables 16-18
6 Q.4 PRODUCT and CLIENT tables 19-21
7 Q.5 MOVIE table 22-23
8 Q.6 Database SPORTS. Table TEAM. Table 24-27
MATCH_DETAILS
1
Acknowledgement
I would like to take this opportunity to express
my heartfelt gratitude to my Computer Science
teacher, Ms. Nupur Gupta for providing her
invaluable guidance, support, and suggestions
throughout the course of the program file. I
would also like to thank my parents and my
friends, whose aid was instrumental in helping
me accomplish my task within the time frame.
The file work has enhanced my knowledge and
helped me learn and evaluate many things.
2
CERTIFICATE
This to certify that, Sanchi of class XIIA
has successfully completed her computer
science program file under the guidance
of her Computer Science teacher, Ms.
Nupur Gupta.
SIGNATURE
3
A.1
Create Database
RYAN
Create table STUDENT
4
Records
inserted
1. Distinct
2. Update
5
3. Alter table ADD
4. Alter table DROP
5. Alter Table MODIFY
6
6. Alter Table CHANGE
8. Alter Table DROP PRIMARY 7. Alter Table ADD PRIMARY KEY
KEY
9. Like
10. Not Like
7
11. Is NULL
12. Is NOT NULL
13. BETWEEN
14. NOT BETWEEN
15. IN
16. NOT IN
8
17. ORDER BY
18. GROUP BY
19. GROUP BY HAVING
20. Aggregate Functions
9
21. DELETE FROM
Create Table ACTIVITY
10
Records inserted
11
1. NATURAL JOIN
2. EQUI JOIN
A) INNER JOIN
B) OUTER JOIN
i) Right Outer Join
ii) Left Outer Join
12
3. CROSS JOIN
13
Q.2
Created table EMP
and records inserted
14
Created table DEPT
and records inserted
a) Show max,
min, avg salary
of managers
b) Count number
of clerks
15
c) Display
designation-wise
list of employees
with name, salary,
and Date of Joining
d) Count number of
employees who are
not getting
commission
e) Show average
salary for all
departments having
salary>2000
f) List count of
employees grouped
by DeptID
g) Display max
salary of employees
in each department
h) Display name of
employees along
with designation
and department
name
i) Count number of
employees working
in ACCOUNTS
department
Q.3
16
Created table
BOOKS and
records inserted
17
Created table
ISSUED and
records inserted
a) Show the books of
‘FIRST PUBL’
written by P. Purohit
b) Display cost of
all books published
for FIRST PUBL
c) Depreciate price
of all books of EPB
Publishers by 5%
18
d) Display name
and price of books,
more than 3 copies
of which have been
issued
e) To show total
cost of books of
each type
f) To show details
of the costliest book
19
Q.4
Created table
PRODUCT
and records
inserted
20
Created table
CLIENT and
records inserted
a) Display
ProductName and
Price for products
whose price is in
the range 50 to 150
b) Display details
of products whose
manufacturer is
either XYZ or ABC
21
c) Display
ProductName,
Manufacturer and
Price for all
products that are
not giving any
discount
d) Display
ProductName and
price for all
products whose
ProductName ends
with ‘h’
e) Display
ClientName, City,
P_ID and
ProductName for
all clients whose
city is Delhi
f) Which column is “P_ID” is the foreign key column for table “CLIENT”.
used as Foreign
Key and name the The P_ID column in the CLIENT table acts as a Foreign Key
table where it has because it references the Primary Key (P_ID) of the
PRODUCT table to link each client to a specific product.
been used as
Foreign Key?
Q.5
22
Created table
MOVIE and
records inserted
23
a) Display all
information from
MOVIE
b) Display the type
of movies
c) Display
Movie_ID,
MovieName,
Total_craning by
showing business
done by the movies
d) Display Movie_ID,
MovieName,
ProductionCost for
movies with
ProductionCost
>150000 and
<1000000
e) Display movie of
type action and
romance
f) Display list of
movies which are
going to release in
February, 2022
24
25
A) Create database
SPORTS
B) Create table
TEAM
C) Show Structure
of table TEAM
D) Insert Records
in table TEAM
26
E) Create table
MATCH_DETAILS
and insert records
a) Display
MatchID, TeamID,
TeamScore,
TeamName where
score>70
b) Display
MatchID,
TeamName and
SecondTeamScore
between 100 to 160
27
c) Display
MatchID,
TeamNames along
with MatchDates
d) Display Unique
TeamNames
e) Display
MatchID and
MatchDAte played
by Anadhi and
Shailab