Scribd
Upload
322 views14 pages

SQL Queries Full

The document shows SQL queries to create a database called 'school' and table called 'teacher' with various fields. It then inserts sample records and performs various queries like selecting, updating, aggregating data from the 'teacher' table. These include sorting, filtering, calculating fields like salaries, finding min, max, averages, joining with another table 'dept', etc.

Uploaded by

Afnan Hashmi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
322 views14 pages

SQL Queries Full

The document shows SQL queries to create a database called 'school' and table called 'teacher' with various fields. It then inserts sample records and performs various queries like selecting, updating, aggregating data from the 'teacher' table. These include sorting, filtering, calculating fields like salaries, finding min, max, averages, joining with another table 'dept', etc.

Uploaded by

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

1.

To create database ‘school’


mysql> create database school;
Query OK, 1 row affected (0.22 sec)
2.To select the database ‘school’ to make it current database
mysql> use school;
Database changed
3.To create a table ‘teacher’ in the current database ‘school’ with the following fields-
Tno(teacher number) of type integer(primary key),tname (20 characters),salary of type
Float,Dept_no of type integer and DOJ of type date
mysql> create table teacher
-> (Tno int primary key,Tname char(20),salary float,dept_no int,DOJ date);
Query OK, 0 rows affected (1.58 sec)
4.To change the data type of DEPT_NO field to character
mysql> alter table teacher modify dept_no char(20);
Query OK, 0 rows affected (1.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
5.To display the structure of the table ‘teacher’

6.To insert the following records

mysql> insert into teacher


-> values(101,"Rakesh Sharma",67900,"D05","2006-12-12");
Query OK, 1 row affected (0.15 sec)
mysql> insert into teacher
-> values(102,"JUGAL MITTAL",60600,"D02","1999-07-03");
Query OK, 1 row affected (0.10 sec)

mysql> insert into teacher


-> values(103,"SHARMILA",61000,"D01","2000-06-06");
Query OK, 1 row affected (0.17 sec)

mysql> insert into teacher


-> values(104,"SANGEETA VATS",66800,"D05","2000-02-20");
Query OK, 1 row affected (0.16 sec)

mysql> insert into teacher


-> values(105,"SANDEEP KAUSHIK",51000,"D03","2001-08-08");
Query OK, 1 row affected (0.10 sec)

mysql> insert into teacher


-> values(106,"RAMESH KUMAR",64000,"D03","1997-07-03");
Query OK, 1 row affected (0.18 sec)

mysql>insert into teacher


-> values(107,"SHAYAM ARORA",64000,"D03","1997-07-03");
Query OK, 1 row affected (0.10 sec)

mysql> insert into teacher


-> values(108,"SHIV OM",46000,"D02","2001-09-22");
Query OK, 1 row affected (0.13 sec)

mysql> insert into teacher


-> values(109,"SHIV OM",53600,"D01","2001-10-12");
Query OK, 1 row affected (0.13 sec)

mysql> insert into teacher


-> values(110,"VIVEK RAWAT",57000,"D01","2000-11-07");
Query OK, 1 row affected (0.17 sec)
7.To display all the records on the screen.

8.To display the teacher no. and name of the teachers whose name starts with letter ‘s’

9.To insert a new record with values 111,”ASHOK SINGHAL”,45000,”1999-08-11” for Tno
TNAME,SALARY,DOJ
mysql> insert into teacher(Tno,Tname,salary,DOJ)
-> values(111,"ASHOK SINGHAL",45000,"1995-08-11");
Query OK, 1 row affected (0.21 sec)
10.To display the record for which DEPT_NO is null

11.To set the value of DEPT_NO as “D04” for the department numbers having null values
And then display all the records
mysql> update teacher
-> set dept_no="D04" where dept_no is null;
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
12.To display the department number available In the school.
13.To increase the salary by 100 and set DOJ as “2006-12-11” for the teacher number 101
And then display all records

14.To display all the records of the teacher whose salary is between 61000 and 68000

15. To display all the records of the teachers whose salary is not between 51000 and
61000
16. To display the teacher number and name of the teachers of DEPT_NO D01,
D04,DO5.(USE IN operator)

17. To display the teachers whose date of joining is after 2005-01-01 and have the salary
more than 60000.

18. To display the records of the teachers whose salary is either less than 50,000 or
belong to department number D05
19. To display the teacher number, name and 20 percent of the salary(given alias
"BONUS")

20. To display the minimum and maximum salary from the table.

21. To display the department number and average salary of each department.

22. To display the teacher number, name and maximum annual salary(alias
"MAXIMUM ANNUAL SALARY") of the teacher.
23. Insert another record with values 112, "CHETAN SHUKLA", 55000, 2001-06-13"
for the field TNO, TNAME, SALARY, DOJ respectively and then display all the
records.

24. To display the total number of teachers.

25. To display the total number of entries in department number field.

26. To display the total number of unique department numbers in the school.
27. To display department wise total salary with total number of departments

28. To display department wise total salary with total number of departments for the
records where total department number count is more than 2.

29. To display the highest salary from each department.

30. To display the highest salary from each department for those teachers whose salary
is more than 60000
31. To display all the records in the sorted order of department number.

32. To display all the records in the descending order of date of joining.

33. To display the teacher number, name and date of joining of the teacher who has
joined latest.
34. To display all the records of the teachers who have joined after the year 2005

35. To display the teacher number, name month and year of joining of the teachers
whose department number is NULL

36. To add a new column ADDRESS to the existing table

37. To change the Column name from ADDRESS to T ADD.

38. To delete the column ADRESS from the table.

39. To add the column ADDRESS after the column NAME

40. To change the name of the table from 'teacher' to 'employee'.

41. To delete the table.


42. Find the output of the following:
(a) SELECT TNO, TNAME, LENGTH(NAME) FROM TEACHER;

(b) SELECT TNO, LOWER(TNAME) FROM TEACHER WHERE


MONTH(DOJ)=12;

(b) SELECT SYSDATE();

(c) SELECT CURDATE() +10;

(d) SELECT DATE(NOW();

43. Create a table 'DEPT' with fields DEPT_NO and DNAME(Department no and
department Name)

44.Insert the following records


45.Display each teacher number,name and dept name from the tables

46.Display the dept name and total salary of each dept in ascending order
SQL
Queries

You might also like