PRACTICAL FILE
Name: VIKKY JAKHAR
TH
CLASS: XII G
ROLL NO.: 08
Index
unit Unit name content
01 DATABASE QUERIES;
CONCEPT 1. CREATE A TABLE
2. INSERT
3. DELETE
4. UPDATE
5. ALTER
6.WHERE COMMAND
[ BETWEEN, LIKE,><]
7. SELECT
8. COUNT (*)
9. AS
10. JOIN CONDITION
11. GROUP BY
12. NESTED.
QUERY
MYSQL
QUES1. Consider the following VIKKY
table:
Table Name: vikky
Write SQL commands for the following;
1. Create the above table.
ANS.
2. Insert values as shown above.
ANS.
3. Delete the employee having EMPLOYEE_ID 1217.
ANS
4. Update the salary of “AMYRA” to 40000.
ANS.
5. Alter the table VIKKY so that null values are not allowed for
the age column.
ANS.
6. Write the query to display the names and salaries of those
employees whose salaries are greater than 20000.
ANS.
7. Write the query to display the details of employees who are
not getting any bonus.
ANS.
8. Write the query to display the names of employees whose
name contains A as the last alphabet.
ANS.
9. Write the query to display names and JOB_TITLE of those
employees whose MANAGER_ID is 1201.
ANS.
10. Write a query to display names and JOB_TITLE of those
employees whose manager is “AMYRA”.
ANS.
11. Write the query to display the names and JOB_TITLE of
those employees aged between 26 years to 30 years (both
inclusive).
ANS.
QUES2. A railway company uses machines to sell tickets.
The machine details and daily sales information are
recorded in two tables:
TABLE NAME: VIKKY_1
TABLE NAME: VIKKY_2
1. Create tables VIKKY_1 and VIKKY_2.
ANS.
VIKKY_1
VIKKY_2
2. Write the query to find the number of the ticket
machine in each station.
ANS
3. Write a query to find the total ticket income of the
station “DELHI” of each day.
ANS
4. Write a query to find the total number of tickets
sold by the machine(MACHINE_ID=122) to date.
ANS
EXTRA QUESTIONS
QUES1. Write the SQL command to create the
table VIKKY_3 with the following constraints;
TABLE NAME: VIKKY_3
QUES2. In a database company there are two
tables given below:
TABLE: VIKKY_3
TABLE: VIKKY_4
Write SQL queries for the following;
1.To display SALESMAN_ID, NAMES,
LOCATION_ID WITH CORRESPONDING
LOCATION_NAME.
2.To display NAMES, SALES, and corresponding
LOCATION_NAME who have achieved sales of
more than 1300000.
3.To display NAMES of those salesmen who have
“SINGH” in their names.
4.Identify the primary key in table VIKKY_4. Give
reason for your choice.
Ans. PRIMARY KEY (Table VIKKY_4) :-
SALESMAN_ID
REASON: It can uniquely identify each row in
table VIKKY_4.
QUES 3. Consider the following VIKKY_6 table:
TABLE NAME: VIKKY_6
TABLE NAME: VIKKY_7
1. Which column is used to relate two
columns?
Ans. TEACHER_ID column is used to connect
two tables.
2. Is it possible to have the primary key and
foreign key both in one table? Justify your
answer with the help of the above table.
Ans. Yes, As in table: VIKKY_6, COURSE_ID can
be used as PRIMARY KEY and
TEACHER_ID is the FOREIGN KEY.
3. With reference to the above table write SQL
command for (a) and (b) and output for (c):
a. To display COURSE_ID, TEACHER_ID, NAMES,
PHONE NUMBER of a teacher living in DELHI.
b. To display TEACHER_ID, NAMES OF TEACHER,
SUBJECTS OF ALL TEACHER with the name of a
teacher starting with “S”.
c. SELECT COURSE_ID, SUBJECT, VIKKY_6.
TEACHER_ID, NAME, PHONE_NUMBER
⮚ FROM VIKKY_6, VIKKY_7
⮚ WHERE VIKKY_6. TEACHER_ID=VIKKY_7.
TEACHER_ID
⮚ AND FEE>=5000;
