Class XI MYSQL

MYSQL
MySQL is the most popular Open Source Relational SQL Database Management
System. MySQL is one of the best RDBMS being used for developing various web-
based software applications.
SQL is a standard language for storing, manipulating and retrieving data in database.
RECORDS
Records are composed of fields, each of which contains one item of information. A set
of records constitutes a file. For example, a personnel file :eg a name field, an
address field, and a phone number field. In relational database management
systems, records are called tuples.
FILE
A field is a single piece of information; a record is one complete set of fields; and
a file is a collection of records.
DATABASE
Database. A database is a data structure that stores organized information.
Most databases contain multiple tables, which may each include several different fields.
For example, a company database may include tables for products, employees, and
financial records.
JDKPS (Database)
File
XI A XI B XI C XI D
Record
1 Name Marks
DATATYPES USED IN MYSQL
CHAR DATA TYPE - Fixed length character data of length
size bytes.
Size-32767 bytes default and minimum size is 1 byte.
VARCHAR DATA TYPE - Variable length character string

having maximum length size bytes.
INTEGER- It requires less storage and provides better

performance than numeric value.
NUMBER(P,S)- Number having precision P and scales S. P

can range from 1-38 and S can range from 84-127.
LONG - Character data of variable length up to 2 gigabytes.
ROW ID - Hexadecimal string representing the unique address
of a row in its table.
What is the difference between drop and delete?
DELETE command is a Data Manipulation Language command

whereas, DROP is a Data Definition Language Command. The point
that distinguishes DELETE and DROP command is that DELETE is
used to remove tuples from a table and DROP is used to remove
entire schema, table, domain.
Table : TEACHER
NO Name Age Department Dateofjoin Salary Gender
Jugal 34 Computer 10-Jan-97 12000 M

1
2 Sharmila 31 History 24-Mar- 20000 F
98
3 Sandeep 32 Maths 12-Dec-96 30000 M
4 Sangeeta 35 History 01-Jul-99 40000 F
5 Rakesh 42 Maths 05-Sep-97 25000 M
6 Shyam 50 History 27-Jun-98 30000 M
7 Shiv om 44 Computer 25-F 21000 M

eb-97
8 Sameer 33 Maths 31-Jul-97 20000 F
(a) To show all informationabout the teacher of History department.

SELECT * FROM TEACHER WHERE Department=”History:;
(b) To list the name of female teachers who are in maths department.
SELECT Name FROM TEACHER WHERE Department=”math and Gender=”F”;
(c) To list names of all teachers with their dat of admission in ascending order.
SELECT Name FROM TEACHER ORDERBY Dateofjoin.
(d) The display teacher’s Name,salary,age for male teacher only.

(e) SELECT Name,Salary,Age FROM TEACHER WHERE Gender=”F”;
(f) To count the number of teachers with age >23.
SELECT COUNT(*) FROM TEACHER WHERE Age>23
(g) To insert a new row in the TEACHER table with the following data;
9,’uma’,’22,’computer’,’13-may-95’,20000,’M’.
INSERT INTO TEACHER VALUES(9,’uma’,’22,’computer’,’13-may-95’,20000,’M’);
(h) Give the output of the following SQL statement:

(I) SELECT COUNT(DISTINCT department)FROM TEACHER;
3
(II) SELECT MAX(age) FROM TEACHER WHERE Gender=’F’;
35
(III) SELECT AVG(salary) FROM TEACHER WHERE Gender=’M’;
20050.00
(IV) SELECT SUM(salary) FROM TEACHER WHERE DATEOFJOIN<,’12-JUL-96’;
2300
Q:1 Write the command to create HOSPITAL table.
Ans1 CREATE TABLE HOSPITAL (PNo Int(4) Primary key,
Name Varchar(20) NOT NULL, Age Int(2) Department
Varchar(18),DateOfAdm Date, Charges Double(7,2), Sex
Char(1));
Q: 2.Write a command to describe the structure of the

above table.
Ans2.DESCRIBE HOSPITAL;
Q:3. To select all the information of patients of cardiology

department.
Ans3. SELECT * FROM HOSPITAL WHERE Department =
'Cardiology’;
Q:4. To list the names of female patients who are in ENT
department.
Ans 4. SELECT name FROM HOSPITAL WHERE Department =
'ENT'AND Sex = 'F';
Q:5. To list names of all patients with their date of

admission in ascending order.
Ans 5. SELECT Name FROM HOSPITAL ORDER BY
DateOfAdm;
Q:6. To display Patient's Name, Charges, Age of only female

patients.
Ans 6. SELECT Name, Charges, Age FROM HOSPITAL WHERE
SEX = 'F:
Q:7. To count the number of patients with Age < 30

Ans 7. SELECT COUNT(*) FROM HOSPITAL WHERE Age <30;
Q:8. Display the department wise total charges.

Ans 8. SELECT Department, SUM(Charges) FROM HOSPITAL
GROUP BY Department;
Q:9.Display the department wise total charges whose
maximum charges more than equal to 300.
Ans 9. SELECT Department, SUM(Charges) FROM HOSPITAL
GROUP BY Department HAVING MAX(Charges) >= 300;
ALTER TABLE HOSPITALADD (Address Varchar(20));
Q:10.Add one more column in the above table as Address

of type char(20).
Ans 10. ALTER TABLE HOSPITAL ADD (Address Varchar(25));
Q:II. Modify the column Address as char(25).

Ans 11. ALTER TABLE HOSPITAL MODIFY COLUMN Address
Varchar(25);
Q:12. Change the name of the column Address to Home

Address.
Ans 12 ALTER TABLE HOSPITAL CHANGE Address Home
Address Varchar(25);
Q:13. Drop the column Home Address

Ans 13. ALTER TABLE HOSPITAL DROP COLUMN Home
Address.
Q:14. SELECT COUNT(DISTINCT Charges) FROM HOSPITAL

Ans COUNT(DISTINCT Charges)
Q:15. SELECT MIN(Age) FROM HOSPITAL WHERE Sex='F,

Ans 15. MIN(Age) 16
Q:16.SELECT SUM(charges)FROM HOSPITAL WHERE

Department =”ENT”
Ans 16. SUM( Charges) 750
Q:17. SELECT AVG(charges)FROM HOSPITAL WHERE

Dateofadm <’2008-02-12’;
Ans 17. AVG(Charges) 383.33333
STUDENTs
RollNo Name Class DOB Gender City Marks
1 Nanda X 6/6/95 M Agra 551
2 Saurabh XII 7/5/93 M Mumbai 462
3 Sanal XI 6/5/94 F Delhi 400
4 Trisla XII 8/8/95 F Mumbai 450
5 Store XII 8/10/95 M Delhi 369
6 Marisla XI 12/12/94 F Dubai 250
7 Neha X 8/12/95 F Moscow 377
8 Nishant X 12/6/95 M Moscow 489
(i) To Display all information about class XII students.

Ans:1 SELECT * from student where class=’XII’;
(ii) List the name of made student of class X.

Ans Select name from students where class=’X’;
(iii) List names all class of all students in descending order of DOB.
Ans: Select * from students order by DOB desc;
(iv) To count the number of student in XII Class of Mumbai city.
Ans: Select count(*) from students where class city=”Mumbai”;
(v) SELECT DISTINCT(City) FROM Student.

Ans: 5
(vi) SELECT AVERAGE(Marks) FROM Student.
Ans: 418.5
Ans: 2
(vii) SELECT MAX(Marks) FROM Student.
Ans: 551

Class XI MYSQL

Uploaded by

Copyright:

Available Formats

Class XI MYSQL

Uploaded by

Document Information

Original Description:

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

Class XI MYSQL

Uploaded by

Copyright:

Available Formats

MYSQL

CHAR DATA TYPE - Fixed length character data of length

Size-32767 bytes default and minimum size is 1 byte.

VARCHAR DATA TYPE - Variable length character string

INTEGER- It requires less storage and provides better

NUMBER(P,S)- Number having precision P and scales S. P

DELETE command is a Data Manipulation Language command

NO Name Age Department Dateofjoin Salary Gender

Jugal 34 Computer 10-Jan-97 12000 M

4 Sangeeta 35 History 01-Jul-99 40000 F

5 Rakesh 42 Maths 05-Sep-97 25000 M

6 Shyam 50 History 27-Jun-98 30000 M

7 Shiv om 44 Computer 25-F 21000 M

(a) To show all informationabout the teacher of History department.

(d) The display teacher’s Name,salary,age for male teacher only.

(h) Give the output of the following SQL statement:

Q: 2.Write a command to describe the structure of the

Q:3. To select all the information of patients of cardiology

Q:5. To list names of all patients with their date of

Q:6. To display Patient's Name, Charges, Age of only female

Q:7. To count the number of patients with Age < 30

Q:8. Display the department wise total charges.

Q:10.Add one more column in the above table as Address

Q:II. Modify the column Address as char(25).

Q:12. Change the name of the column Address to Home

Q:13. Drop the column Home Address

Q:14. SELECT COUNT(DISTINCT Charges) FROM HOSPITAL

Q:15. SELECT MIN(Age) FROM HOSPITAL WHERE Sex='F,

Q:16.SELECT SUM(charges)FROM HOSPITAL WHERE

Q:17. SELECT AVG(charges)FROM HOSPITAL WHERE

RollNo Name Class DOB Gender City Marks

1 Nanda X 6/6/95 M Agra 551

2 Saurabh XII 7/5/93 M Mumbai 462

3 Sanal XI 6/5/94 F Delhi 400

4 Trisla XII 8/8/95 F Mumbai 450

5 Store XII 8/10/95 M Delhi 369

6 Marisla XI 12/12/94 F Dubai 250

7 Neha X 8/12/95 F Moscow 377

8 Nishant X 12/6/95 M Moscow 489

(i) To Display all information about class XII students.

(ii) List the name of made student of class X.

(v) SELECT DISTINCT(City) FROM Student.

You might also like