[go: up one dir, main page]
Scribd
Upload
248 views15 pages

PRACTICAL FILE - Term-1

The document contains information about a practical file submitted for the subject Information Technology. It includes an index listing activities completed on different dates along with the teacher's signature. It also provides details about introducing DBMS and MySQL, database languages like DDL, DML, DCL and TCL. Finally, it describes two database activities involving creating and querying tables to demonstrate SQL commands.

Uploaded by

Vinika Ahuja
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
248 views15 pages

PRACTICAL FILE - Term-1

The document contains information about a practical file submitted for the subject Information Technology. It includes an index listing activities completed on different dates along with the teacher's signature. It also provides details about introducing DBMS and MySQL, database languages like DDL, DML, DCL and TCL. Finally, it describes two database activities involving creating and querying tables to demonstrate SQL commands.

Uploaded by

Vinika Ahuja
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 15

PRACTICAL FILE (Term-1)

OF
INFORMATION TECHNOLOGY
Subject Code: - 802

SUBMITTED TO: SUBMITTED BY:

Ms. Satinder Kaur Student Name:-


Roll No:-
Class:- XII
Stream:-
INDEX

Sr. Page
Date of Practical Topic Teacher’s Signature
No. No.

1. 12/05/2021 Introduction to DBMS 1-2


Introduction to MySQL
➢ Definition
➢ Hardware Requirement
2. 18/05/2021 ➢ Software Requirement 3-5
Database Languages
➢ DDL
➢ DML
➢ DCL
3. 20/05/2021 ➢ TCL

4. 25/05/2021 Database Activity -1

5. 02/06/2021 Database Activity - 2


Introduction to DBMS
Database Management System

Relational database management system (RDBMS)

MySQL
Software and Hardware requirement for MYSQL
Hardware Requirement

Software Requirement
➢Database Languages
❖Data Definition Language
• CREATE:This command is used to create a new table or relation. The syntax for this command is:

CREATE TABLE<table name>


(
<column 1><data type> [constraint] ,
<column 2><data type>[constraint],
<column 3><data type>[constraint]
);Where [] is optional.

• ALTER:

• DROP:

• RENAME:
❖Data Manipulation Language
• SELECT:

• INSERT:

• UPDATE:

• DELETE:
❖Data Control Language
• GRANT:

• REVOKE:

❖Transaction Control Language


• COMMIT

• SAVEPOINT:

• ROLLBACK:
Database Activity-1

Consider the following table / relation WORKER.

WORKER_ID FIRST_NAME LAST_NAME SALARY JOINING_DATE DEPARTMENT


001 Monika Arora 100000 2014-02-20 HR
09:00:00
002 Niharika Verma 80000 2014-06-11 Admin
09:00:00
003 Vishal Singhal 300000 2014-02-20 HR
09:00:00
004 Amitabh Singh 500000 2014-02-20 Admin
09:00:00
005 Vivek Bhati 500000 2014-06-11 Admin
09:00:00
006 Vipul Diwan 200000 2014-06-11 Account
09:00:00
007 Satish Kumar 75000 2014-01-20 Account
09:00:00
008 Geetika Chauhan 90000 2014-04-11 Admin
09:00:00

Write SQL commands for the following queries based on the above relations:

1) Write an SQL query to create the above table and also insert the values in to it.

CREATE TABLE Worker (

WORKER_ID INT(3)PRIMARY KEY,

FIRST_NAME CHAR(25),

LAST_NAME CHAR(25),

SALARY INT(15),

JOINING_DATE DATETIME,

DEPARTMENT CHAR(25));

…………………………………………………………………….

INSERT INTO Worker WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES

(001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR');


2) Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>.

3) Write an SQL query to fetch unique values of DEPARTMENT from Worker table.

4) Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending.

5) Write an SQL query to print all Worker details from the Worker table order by FIRST_NAME Ascending
and DEPARTMENT Descending.

6) Write an SQL query to print details for Workers with the first name as “Vipul” and “Satish” from Worker
table.

7) Write an SQL query to print details of workers excluding first names, “Vipul” and “Satish” from Worker
table.
8) Write an SQL query to print details of Workers with DEPARTMENT name as “Admin”.

9) Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘a’.

10) Write an SQL query to print details of the Workers whose FIRST_NAME ends with ‘h’ and contains six
alphabets.

11) Write an SQL query to print details of the Workers whose SALARY lies between 100000 and 500000.

12) Write an SQL query to print details of the Workers who have joined in Feb’2014.

13) Write an SQL query to fetch the count of employees working in the department ‘Admin’.
14) Write an SQL query to fetch the no. of workers for each department in the descending order.

15) Write an SQL query to fetch duplicate records having matching data in some fields of a table.

16) Write an SQL query to fetch departments along with the total salaries paid for each of them.
Database Activity-2

Consider the following tables CARDEN and CUSTOMER. Write SQL commands for the
following statements:

TABLE: CARDEN

Code CarName Make Color Capacity Charges

501 A-Star Suzuki RED 3 14

503 Indigo Tata SILVER 3 12

502 Innova Toyota WHITE 7 15

509 SX4 Suzuki SILVER 4 14

TABLE: CUSTOMER

CCode Cname Ccode

1001 Hemant Sahu 501

1002 Raj Lal 509

1002 Feroza Shah 503

1004 Ketan Dhal 502

I. To display the customer name and the corresponding name of the cars hired by them

Ans: Select Cname,Carname from CUSTOMER cus, CARDEN car where cus.Ccode=car.Ccode

II. To display the names of all the silver colored


III. To display name of car, make and capacity of cars in descending order of their sitting

IV. To display the highest charges at which a vehicle can be hired from

You might also like