SQL New Test Queries
SQL New Test Queries
SQL New Test Queries
subjects they study, the teachers who teach those subjects and also the marks students scored on
each of those subjects.
So the first obvious thing we will do is to create a database named school
Inside our Database we will create four tables:
a) Students: for storing students information like student id, students name, gender, contact
number of parent, etc.
b) Teachers: for storing teacher information like teacher id, teacher name, gender, contact
number of teacher, etc.
c) Subjects: for storing subject name and id of teacher teaching that subject
4) Scores: for storing details like student id (to identify student based on student id of students
table), subject id (to identify subject based on subject id of subjects table) and score (i.e. marks
scored by student on that subject)
IMPORTANT:
So if you take a minute out and think about how the tables are related to each other then we have
students table and teachers table which are independent, then there is subjects table which uses
teacher_id so it is dependent on teachers table and scores table which are connected to two tables
(students table through student_id and subjects table through subject_id)
It is important to remember the relation between tables as they are very useful when we have to
think about joins.
So now that we have the entire picture in mind, lets get started with creating the Database and
tables for it:
1) Create New Database with Name school
2) Create a New Table called 'students' inside Database School with following fields /
columns:
a) student_id (type int(11) , Should be Primary Key + Should Auto Increment on its
own )
b) student_name (type varchar (200) and it cannot be null)
c) student_group (which can be blue, green, yellow or red. Type enum and again it
cannot be null)
d) student_gender (type enum('M','F') Where M stands for Male and F for female
and cannot be NULL )
e) parent_contact_num ( type bigint(10) and cannot be null )
3) Lets populate the students table by inserting few student details inside the table ( Just
copy the Entire INSERT query below and run it )
5) Lets Populate the teachers table by inserting few student details inside it ( Copy the
Entire INSERT Query below and run it )
INSERT INTO teachers( teacher_name , teacher_gender , teacher_contact )
VALUES ( 'Janet' , 'F' , 3343345566 ),
( 'John' , 'M' , 7657657650 ),
( 'Marcus' , 'M' , 4343434343 ),
( 'Teresa' , 'F' , 6506507788 ),
( 'Jim' , 'M' , 3243245500 );
Teachers will automatically be assigned teacher_id's from 1 to 5
6) Create a New Table Called 'subjects' inside Database School with following fields /
columns:
a) subject_id ( type int(10) , should be primary key + should auto increment on its
own )
b) teacher_id ( type int(10) )
c) subject_name ( type varchar(200) and it cannot be null )
7) Now we want teacher_id field in table subjects to be foreign key of field teacher_id in
table teachers. Do it using ALTER?
8) Lets Populate table Subjects? (Copy the Entire INSERT Query below and run it )
INSERT INTO subjects( teacher_id , subject_name )
VALUES( 1 , 'Science' ),
( 1 , 'Computers' ),
( 2 , 'Math' ),
( 3 , 'History' ),
( 2 , 'Economics' ),
( 4 , 'Arts' ),
( 5 , 'Sports' ),
( 3 , 'Geography' );
Subjects will automatically assign subject_id from 1 to 8
It will not work because we have added a FOREIGN KEY Constraint on teacher_id
Column of table subjects which points to teacher_id column of table teachers and since
there is no teacher with teacher_id of 9, it will not be allow it to run.
11) Create FOREIGN Key constraint such that student_id in table scores points to
student_id in students table and subject_id in table scores points to subject_id in table
subjects
12) Lets Populate Scores Table i.e. we are inserting the marks scored by students in all
subjects ( Copy the Entire INSERT Query below and run it )
SELECT student_name,
student_gender
FROM students;
14) Find id, name and group of all students whose id is greater than 5 and belongs to group
red?
SELECT student_id,
student_name,
student_group
FROM students
WHERE student_id > 5 AND student_group = 'red';
15) Find all teachers whose id are either 1,3 or 5 and are all male?
SELECT *
FROM teachers
WHERE teacher_id IN ( 1 , 3 , 5 )
AND teacher_gender = 'M';
16) Find all scores where student score more than 70 in subject_id 7 OR students scored
more than 50 in subject_id 5
SELECT *
FROM scores
WHERE ( score > 70 AND subject_id = 7 ) OR
( score > 50 AND subject_id = 5 );
SELECT *
FROM scores
ORDER BY score DESC;
18) Get name of all subject taught by teacher_id 1 or teacher_id 3 in two different ways?
Method # 1:
SELECT subject_name
FROM subjects
WHERE teacher_id = 1 OR teacher_id = 3;
Method # 2:
SELECT subject_name
FROM subjects
WHERE teacher_id IN ( 1 , 3 );
19) Find Details of all subjects that either start with S or end with S?
SELECT *
FROM subjects
WHERE subject_name LIKE 's%' OR subject_name LIKE '%s';
20) Get Details of all subjects which are taught by female Teachers?
SELECT *
FROM teachers t
LEFT JOIN subjects s ON t.teacher_id = s.teacher_id
WHERE t.teacher_gender = 'F';
SELECT *
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
WHERE stu.student_id = 3;
SELECT stu.name,
MAX( s.score )
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
GROUP BY stu.student_id;
23) List Name of each subject and highest score scored for the same subject?
SELECT sub.subject_name,
MAX( s.score )
FROM subjects sub
LEFT JOIN scores s ON sub.subject_id = s.subject_id
GROUP BY sub.subject_id;
24) List name of all subjects, name of student and score for that subject scored by students
whose student_id is either 1 or 3? Do it using UNION?
SELECT sub.subject_name,
stu.student_name,
s.score
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
LEFT JOIN subjects sub ON s.subject_id = sub.subject_id
WHERE stu.student_id = 1
UNION
SELECT sub.subject_name,
stu.student_name,
s.score
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
LEFT JOIN subjects sub ON s.subject_id = sub.subject_id
WHERE stu.student_id = 3;
25) Get a total score scored by each student, Average scored by each student along with
their names and order them in descending order of total and we only want to see top 5?
SELECT stu.student_name,
SUM( s.score ) AS total_score ,
AVG( s.score ) AS avg_score
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
GROUP BY stu.student_id
ORDER BY SUM( s.score ) DESC
LIMIT 5;
26) Get a list of all teachers and avg score scored by students on subject being taught by
that teacher and order them in ascending order by average score?
SELECT t.teacher_name,
AVG( s.score )
FROM teachers t
LEFT JOIN subjects sub ON t.teacher_id = sub.teacher_id
LEFT JOIN scores s ON s.subject_id = sub.subject_id
GROUP BY t.teacher_id
ORDER BY AVG( s.score );
27) Write a Query to update score of student with student_id 5 in subject_id 4 to 95?
UPDATE scores
SET score = 95
WHERE student_id = 5 AND subject_id = 4;
28) Write a Query to Find All Students who have scored an Average of More than 60 and
order them in decreasing order of their average scores?
SELECT stu.student_name,
stu.student_id,
AVG( s.score )
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
GROUP BY stu.student_id
HAVING AVG( s.score ) > 60
ORDER BY AVG( s.score );
29) Select Name of all Students who have score More than 90 in at least two subjects? We
only want Unique names i.e. even if student has score more than 75 in say three subjects,
still his or her name should appear only once in the result.
30) Create View using query for Question #29 and name it as bright_students? Also how
can we see the view result?
Creating View:
CREATE VIEW bright_students AS
SELECT DISTINCT stu.student_name
FROM students stu
LEFT JOIN scores s ON stu.student_id = s.student_id
WHERE s.score > 90
GROUP BY stu.student_id
HAVING COUNT(*) >= 2;
View Result:
SELECT *
FROM bright_students;
I am sure you guys were able to do most of it and I can assure you that you WILL NOW be able
to write any Real World query yourself.
Thank you.