SQL is a standard language for storing, manipulating and retrieving data in databases.
In this series of posts, we will discuss the SQL queries from very Basic to Advanced.
It is recommended to view the post in the following order:
1. SQL Basic Queries Example – I
2. SQL Basic Queries Example – II
3. SQL Queries Using Aggregate Functions – I
4. SQL Queries Using Aggregate Functions – II
5. SQL Intermediate Queries Example – I
6. SQL Intermediate Queries Example – II
7. SQL Advanced Queries Example – I
8. SQL Advanced Queries Example – II
In this post, we will look at the basic SQL queries.
Create the tables described below:
Table Name: department
+--------+------------------------+ | DeptID | DeptName | +--------+------------------------+ | 1 | Information Technology | | 2 | Electrical | | 3 | Civil | | 4 | Mechanical | | 5 | Chemical | +--------+------------------------+
Command:
create table department( DeptID int primary key not null, DeptName varchar(40) ); insert into department values(1, 'Information Technology'); insert into department values(2, 'Electrical'); insert into department values(3, 'Civil'); insert into department values(4, 'Mechanical'); insert into department values(5, 'Chemical');
Table Name: stud_member
+--------+--------+-------------+--------+--------+----------+-----------+--------+ | RollNo | FName | MName | SName | DeptID | Semester | ContactNo | Gender | +--------+--------+-------------+--------+--------+----------+-----------+--------+ | 1 | Ankur | Samir | Kahar | 1 | 1 | 272121 | M | | 2 | Dhaval | Dhiren | Joshi | 1 | 1 | 232122 | M | | 3 | Ankita | Biren | Shah | 1 | 1 | 112121 | F | | 10 | Komal | MaheshKumar | Pandya | 2 | 3 | 123123 | F | | 13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667 | M | | 23 | Jinal | Ashish | Gandhi | 2 | 1 | 323232 | M | | 22 | Ganesh | Asha | Patel | 2 | 3 | 124244 | M | | 4 | Shweta | Mihir | Patel | 3 | 1 | 646341 | F | | 7 | Pooja | Mayaank | Desai | 3 | 3 | 328656 | F | | 8 | Komal | Krishnaraj | Bhatia | 2 | 3 | 257422 | F | | 43 | Kiran | Viraj | Shah | 1 | 1 | 754124 | F | +--------+--------+-------------+--------+--------+----------+-----------+--------+
Command:
create table stud_member( RollNo int, FName varchar(20), MName varchar(20), SName varchar(20), DeptID int, foreign key(DeptID) references department(DeptID), Semester int, ContactNo int, Gender varchar(2) ); insert into stud_member values(1,'Ankur','Samir','Kahar',1,1,272121,'M'); insert into stud_member values(2,'Dhaval','Dhiren','Joshi',1,1,232122,'M'); insert into stud_member values(3,'Ankita','Biren','Shah',1,1,112121,'F'); insert into stud_member values(10,'Komal', 'MaheshKumar', 'Pandya', 2, 3, 123123, 'F'); insert into stud_member values(13, 'Amit' ,'Jitenkumar' ,'Mehta' ,3 ,3, 453667, 'M'); insert into stud_member values(23, 'Jinal', 'Ashish', 'Gandhi', 2, 1, 323232, 'M'); insert into stud_member values(22, 'Ganesh', 'Asha', 'Patel', 2, 3, 124244, 'M'); insert into stud_member values(4, 'Shweta', 'Mihir', 'Patel', 3, 1, 646341, 'F'); insert into stud_member values(7, 'Pooja', 'Mayaank', 'Desai', 3, 3, 328656, 'F'); insert into stud_member values(8, 'Komal', 'Krishnaraj', 'Bhatia', 2, 3, 257422, 'F'); insert into stud_member values(43, 'Kiran', 'Viraj', 'Shah', 1, 1, 754124, 'F');
Exercise on basic Queries
a) Display the names and contact numbers of all student members.
Command: select SName, ContactNo from stud_member; Output: +--------+-----------+ | SName | ContactNo | +--------+-----------+ | Kahar | 272121 | | Joshi | 232122 | | Shah | 112121 | | Pandya | 123123 | | Mehta | 453667 | | Gandhi | 323232 | | Patel | 124244 | | Patel | 646341 | | Desai | 328656 | | Bhatia | 257422 | | Shah | 754124 | +--------+-----------+
b) Give the names and roll number of all students of Information Technology
who are the members
Command: select SName, RollNo from stud_member a natural join department b where b.DeptName = 'Information Technology'; Output: +-------+--------+ | SName | RollNo | +-------+--------+ | Kahar | 1 | | Joshi | 2 | | Shah | 3 | | Shah | 43 | +-------+--------+
c) Display names of Departments whose students are members.
Command: select b.DeptID,b.DeptName from stud_member a natural join department b group by DeptName; Output: +--------+------------------------+ | DeptID | DeptName | +--------+------------------------+ | 3 | Civil | | 2 | Electrical | | 1 | Information Technology | +--------+------------------------+
d) Display names of Departments for which no student are members.
Command: select * from department where department.DeptID NOT IN (select DeptID from stud_member); Output: +--------+------------+ | DeptID | DeptName | +--------+------------+ | 4 | Mechanical | | 5 | Chemical | +--------+------------+
e) Display names of all Departments.
Command: select DeptName from department; Output: +------------------------+ | DeptName | +------------------------+ | Information Technology | | Electrical | | Civil | | Mechanical | | Chemical | +------------------------+
f) Find the number of students in the Electrical Department who are members.
Command: select count(*) from stud_member a natural join department b where b.DeptName = 'Electrical'; Output: +----------+ | count(*) | +----------+ | 4 | +----------+
g) Display information of student members whose name begins with the letter ‘A’.
Command: select * from stud_member where FName like 'A%'; Output: +--------+--------+------------+-------+--------+----------+-----------+--------+ | RollNo | FName | MName | SName | DeptID | Semester | ContactNo | Gender | +--------+--------+------------+-------+--------+----------+-----------+--------+ | 1 | Ankur | Samir | Kahar | 1 | 1 | 272121 | M | | 3 | Ankita | Biren | Shah | 1 | 1 | 112121 | F | | 13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667 | M | +--------+--------+------------+-------+--------+----------+-----------+--------+
h) Display all details of Male members only.
Command: select * from stud_member where Gender = 'M'; Output: +--------+--------+------------+--------+--------+----------+-----------+--------+ | RollNo | FName | MName | SName | DeptID | Semester | ContactNo | Gender | +--------+--------+------------+--------+--------+----------+-----------+--------+ | 1 | Ankur | Samir | Kahar | 1 | 1 | 272121 | M | | 2 | Dhaval | Dhiren | Joshi | 1 | 1 | 232122 | M | | 13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667 | M | | 23 | Jinal | Ashish | Gandhi | 2 | 1 | 323232 | M | | 22 | Ganesh | Asha | Patel | 2 | 3 | 124244 | M | +--------+--------+------------+--------+--------+----------+-----------+--------+
i) Display data of student members who are currently in semester 3.
Command: select * from stud_member where Semester = 3; Output: +--------+--------+-------------+--------+--------+----------+-----------+--------+ | RollNo | FName | MName | SName | DeptID | Semester | ContactNo | Gender | +--------+--------+-------------+--------+--------+----------+-----------+--------+ | 10 | Komal | MaheshKumar | Pandya | 2 | 3 | 123123 | F | | 13 | Amit | Jitenkumar | Mehta | 3 | 3 | 453667 | M | | 22 | Ganesh | Asha | Patel | 2 | 3 | 124244 | M | | 7 | Pooja | Mayaank | Desai | 3 | 3 | 328656 | F | | 8 | Komal | Krishnaraj | Bhatia | 2 | 3 | 257422 | F | +--------+--------+-------------+--------+--------+----------+-----------+--------+
j) Display data of student female members in alphabetical order.
Command: select * from stud_member where Gender = 'F' order by FName; Output: +--------+--------+-------------+--------+--------+----------+-----------+--------+ | RollNo | FName | MName | SName | DeptID | Semester | ContactNo | Gender | +--------+--------+-------------+--------+--------+----------+-----------+--------+ | 3 | Ankita | Biren | Shah | 1 | 1 | 112121 | F | | 43 | Kiran | Viraj | Shah | 1 | 1 | 754124 | F | | 10 | Komal | MaheshKumar | Pandya | 2 | 3 | 123123 | F | | 8 | Komal | Krishnaraj | Bhatia | 2 | 3 | 257422 | F | | 7 | Pooja | Mayaank | Desai | 3 | 3 | 328656 | F | | 4 | Shweta | Mihir | Patel | 3 | 1 | 646341 | F | +--------+--------+-------------+--------+--------+----------+-----------+--------+
