Relational Database is a collection of logically related relations/tables.
Table/Relation is a collection of logically related rows.
(The name 'relation' because columns and rows are all logically related)
Row/record/tuple is a collection of logically related columns.
Column/field/attribute/key contains a data values i.e. one piece of information.
Structured information is a tabular information wherein information is properly laid out or classified or segregated. It allows
an easy access to the information.
Folio Name Clas DOJ
s
123 Abcd 11C 20200812
456 Xyz 12A 20190728
...
Relational Database Management System (RDBMS) is a software that is used to create and manage a database and its
objects. e.g. MySQL, Oracle, MSSQL
SQL (Structured Query Language) is a language that is used by an RDBMS to create and manage database and its objects.
Typical work flow of creating and managing a database using SQL queries
Create a database (just once)
Use the database (every time while working on tables)
Create a table (set the table structure - define column names, their data types, sizes and constraints)(SQL datatypes
include – int, decimal, char, varchar, date)
Insert rows (add or input data into the tables)
Select rows (display records)
Update rows (change data values)
Change (alter) the table structure (by adding or removing columns)
Delete rows (remove the data values from the table)
Drop table and database (remove the containers)
Prerequisites of working with SQL
Download and install XAMPP (just once)
To start working with SQL, everytime, in CMD type the following command to start the MySQL server
C:\xampp\mysql\bin>mysql -u root –p
Enter password when prompted for
(type cd C:\xampp\mysql\bin at command the prompt to go to bin subdirectory)
To create a MySQl server user 'u1' with password 'p1'
GRANT ALL PRIVILEGES ON *.* TO 'u1'@'localhost' IDENTIFIED BY 'p1'
Type SQL queries to perform RDBMS related tasks in front of MYSQL prompt (something like MariaDB [d1]>
where 'd1' is the database name, initially the prompt will read as MariaDB [(none)] indicating that no database is
in use.)
Type 'exit' to close the MySQL server
SQL Queries
1. Create a new database (just once!)
CREATE DATABASE mydatabase;
2. Open an existing database (so that one can create or use existing tables in it)
USE mydatabase;
3. Create a table 'student' with the following columns:
roll, name, house, class, gender, dob, doj, fee, marks, garde, result
#primary key ensures that values are unique
CREATE TABLE student(
roll INT PRIMARY KEY,
name VARCHAR(30),
house VARCHAR(30),
class VARCHAR(3),
gender CHAR,
dob DATE,
doj DATE,
fee DECIMAL(10,2),
marks INT,
garde VARCHAR(2),
result VARCHAR(30)
);
4. Insert a new record into the table student.
(for INT/DECIMAL/DATE – write values without quotes and for VARCHAR/CHAR – write values within quotes)
INSERT INTO student VALUES(1, 'Ritul', 'Pant', '11A', 'F', 20010322, 20180413, 54000.00, 78,
'B1', 'PASS');
or
INSERT INTO student(name,roll,house,class) VALUES('Ritul',1,'Pant','11A');
5. Insert/Add a new field city into the table student.
ALTER TABLE student ADD COLUMN city VARCHAR(30); #changes table structure
...and update the city for all the existing rows e.g. as:
unoconv_2616343965.docx 1
,UPDATE student SET city='Delhi' WHERE roll=123; #changes data values
6. List/Display/show/print all records from the table student. ('*' means all columns) (If not using WHERE clause,
query picks up all the rows)
SELECT * FROM student; #* means all columns and NOT using WHERE means all rows
7. Change/update the class of a student.
UPDATE student SET class='12A' WHERE roll=1;
8. Shift all class 10A students to 11A.
UPDATE student SET class='11A' WHERE class='10A';
9. List the number of students coming from Delhi.
SELECT count(*) FROM student WHERE city='DELHI';
10. List the students coming from Delhi.
SELECT * FROM student WHERE city='DELHI';
11. List the student records if name starts with 'Kumar'.
SELECT * FROM student WHERE name LIKE 'Kumar%';
12. List the student records if name ends with 'Kumar'.
SELECT * FROM student WHERE name LIKE '%Kumar';
13. List the student records if name contains the word 'Kumar'.
SELECT * FROM student WHERE name LIKE '%Kumar%';
14. List the students who have joined after 1st of April 2018. DATE FORMAT => YYYYMMDD
SELECT name FROM student WHERE doj > 20180401;
15. List students if they have joined the school in 2015.
SELECT * FROM student WHERE YEAR(doj) = 2015;
16. List students if they have joined the school in April of 2015.
SELECT * FROM student WHERE MONTH(doj) = 4 AND YEAR(doj) = 2015;
17. List the classes in the school.
SELECT DISTINCT class FROM student;
18. Display the cities, the school has students from. (How many cities we have the students from?)
SELECT DISTINCT city FROM student;
19. Display the class 11A students.
SELECT * FROM student WHERE class='11A';
20. Display the class 11 students......Display the class 11A and 11C students.
SELECT * FROM student WHERE class='11A' AND OR class='11C';
OR
SELECT * FROM student WHERE class IN('11A','11C')
or
SELECT * FROM student WHERE class LIKE '11%';
21. Display the class 11A students of Tilak house.
SELECT *FROM student WHERE house='Tilak' AND class='11A';
22. Display the class 11A students of Tilak house if their marks are less than 40.
SELECT * FROM student WHERE house='Tilak' AND class='11A'
AND marks < 40;
marks between 40 and 60
23. Remove/delete the student record if class is 11C.
DELETE FROM student WHERE class='11C';
Remove/delete the student record if roll is 23.
DELETE FROM student WHERE roll=23;
24. Upgrade the class of all class 10 students.
UPDATE student SET class='11' WHERE class LIKE '10%';
25. Display the first 3 characters of each student name.
SELECT LEFT(name, 3) FROM student;
26. Display the last 3 characters of each student name.
SELECT RIGHT(name, 3) FROM student;
27. Display the number of characters in each student name.
SELECT name, LENGTH(name) FROM student;
28. List the students who have scored marks between 80 and 90.
SELECT * FROM student WHERE marks BETWEEN 80 AND 90;
or
SELECT * FROM student WHERE marks >=80 AND OR marks<=90;
29. Display the student names if 4th & 5th characters are 'JA'.
SELECT name FROM student WHERE MID(name,4,2)='JA';
30. Display the name if it contains at least 5 characters.
SELECT name FROM student WHERE LENGTH(name)>=5;
or
SELECT name FROM student WHERE name LIKE '_ _ _ _ _%'; WILD CARDS
31. Display the student records if name if lengthier than 20 characters.
SELECT * FROM student WHERE LENGTH(name)>20;
32. Round off the fee of each student. Also find the square root of fee.
SELECT ROUND(fee), SQRT(fee) FROM student;
33. Round off the fee of each student, to two places.
SELECT ROUND(fee,2) FROM student;
unoconv_2616343965.docx 2
, 34. Display the student records if their marks are even numbers.
SELECT * FROM student WHERE MOD(marks, 2)=0;
35. Drop the table student.
DROP TABLE student; USE IT WITH EXTRA CAUTION!!!
36. Increase the marks of every student by 10.
UPDATE student SET marks=marks+10;
37. Increase the marks of every student by 10% if it is less than 40.
UPDATE student SET marks=marks+marks*10/100 WHERE marks < 40;
38. Change the city name to New Delhi if it is Delhi.
UPDATE student SET city='New Delhi' WHERE city='Delhi';
39. Count the number of students.
SELECT COUNT(*) FROM student;
and
SELECT COUNT(name) FROM student; (this might give a different count!)
40. Count the number of students class-wise.
SELECT class, COUNT(class) FROM student GROUP BY class;
41. Display the table structure.
DESC student;
42. Display the tables in a database.
SHOW TABLES;
43. Delete all records of Delhi students.
DELETE FROM student WHERE city='Delhi';
DELETE FROM student will remove all the records, so be careful
44. Open the database named d123.
USE d123;
45. Display student names alphabetically.
SELECT name FROM student ORDER BY name;
46. Display student records house wise (in ascending order) and class wise (in descending order).
SELECT name FROM student ORDER BY house asc, class desc;
47. Remove the database d123.
DROP DATABASE d123;
48. Create a table s1 with roll and name where roll is a primary key.
CREATE TABLE s1(roll INT PRIMARY KEY, name VARCHAR(30));
49. Add the primary key constraint to the roll column of an existing table s2.
ALTER TABLE s2 ADD CONSTRAINT pk PRIMARY KEY (roll);
50. Remove the primary key constraint from the table s2.
ALTER TABLE s2 DROP PRIMARY KEY;
51. Create table s3 with following constraints - roll PK, name not null, age >0, folio unique, class default 10A.
CREATE TABLE s3(roll int PRIMARY KEY, name varchar(30) NOT NULL, age INT
CHECK(age>0), folio int UNIQUE, class VARCHAR(3) DEFAULT '10A');
AGGREGATE FUNCTIONS
Multiple Row Functions or Aggregate Functions operate on a set of rows to return a single value.
52. Find the highest and lowest marks scored in the database table and display it as 'Highest Marks' and 'Lowest Marks'.
SELECT MAX(marks) as 'Highest Marks', MIN(marks) as 'Lowest Marks' FROM student;
53. Find the average marks.
SELECT AVG(marks) as 'Average Marks' FROM student;
54. Find the total fee collected.
SELECT SUM(fee) as 'Total Fee' FROM student;
55. Find the number of students in the table.
SELECT COUNT(*) as 'No. of Students' FROM student;
56. Find the number of students in the table if their result has been declared.
SELECT COUNT(result) as 'No. of Results Generated' FROM student;
or
SELECT COUNT(*) as 'No. of Results Generated' FROM student WHERE result IS NOT NULL;
57. Find the number of students in the table whose result has not been declared.
SELECT COUNT(*) as 'No. of Results NOT Generated' FROM student WHERE result IS NULL;
QUERYING DATA USING GROUP BY, HAVING, ORDER BY
58. Find the number of students in each class.
SELECT class, COUNT(class) FROM student GROUP BY class;
59. Find the number of students in classes '6A', '7A', and '8A'.
(DON'T USE WHERE, INSTEAD USE HAVING TO CHECK A CONDITION WHILE USING GROUP BY)
SELECT class, COUNT(class) FROM student GROUP BY class HAVING class IN('6A','7A','8A');
60. Find the number of students in classes other than '6A', '7A', and '8A'.
SELECT class, COUNT(class) FROM student GROUP BY class HAVING class NOT IN('6A','7A','8A');
61. Display the student records class-wise.
SELECT * FROM student ORDER BY class;
62. Display the student records class-wise arranged alphabetically based on their names.
SELECT * FROM student ORDER BY class, name;
unoconv_2616343965.docx 3
Table/Relation is a collection of logically related rows.
(The name 'relation' because columns and rows are all logically related)
Row/record/tuple is a collection of logically related columns.
Column/field/attribute/key contains a data values i.e. one piece of information.
Structured information is a tabular information wherein information is properly laid out or classified or segregated. It allows
an easy access to the information.
Folio Name Clas DOJ
s
123 Abcd 11C 20200812
456 Xyz 12A 20190728
...
Relational Database Management System (RDBMS) is a software that is used to create and manage a database and its
objects. e.g. MySQL, Oracle, MSSQL
SQL (Structured Query Language) is a language that is used by an RDBMS to create and manage database and its objects.
Typical work flow of creating and managing a database using SQL queries
Create a database (just once)
Use the database (every time while working on tables)
Create a table (set the table structure - define column names, their data types, sizes and constraints)(SQL datatypes
include – int, decimal, char, varchar, date)
Insert rows (add or input data into the tables)
Select rows (display records)
Update rows (change data values)
Change (alter) the table structure (by adding or removing columns)
Delete rows (remove the data values from the table)
Drop table and database (remove the containers)
Prerequisites of working with SQL
Download and install XAMPP (just once)
To start working with SQL, everytime, in CMD type the following command to start the MySQL server
C:\xampp\mysql\bin>mysql -u root –p
Enter password when prompted for
(type cd C:\xampp\mysql\bin at command the prompt to go to bin subdirectory)
To create a MySQl server user 'u1' with password 'p1'
GRANT ALL PRIVILEGES ON *.* TO 'u1'@'localhost' IDENTIFIED BY 'p1'
Type SQL queries to perform RDBMS related tasks in front of MYSQL prompt (something like MariaDB [d1]>
where 'd1' is the database name, initially the prompt will read as MariaDB [(none)] indicating that no database is
in use.)
Type 'exit' to close the MySQL server
SQL Queries
1. Create a new database (just once!)
CREATE DATABASE mydatabase;
2. Open an existing database (so that one can create or use existing tables in it)
USE mydatabase;
3. Create a table 'student' with the following columns:
roll, name, house, class, gender, dob, doj, fee, marks, garde, result
#primary key ensures that values are unique
CREATE TABLE student(
roll INT PRIMARY KEY,
name VARCHAR(30),
house VARCHAR(30),
class VARCHAR(3),
gender CHAR,
dob DATE,
doj DATE,
fee DECIMAL(10,2),
marks INT,
garde VARCHAR(2),
result VARCHAR(30)
);
4. Insert a new record into the table student.
(for INT/DECIMAL/DATE – write values without quotes and for VARCHAR/CHAR – write values within quotes)
INSERT INTO student VALUES(1, 'Ritul', 'Pant', '11A', 'F', 20010322, 20180413, 54000.00, 78,
'B1', 'PASS');
or
INSERT INTO student(name,roll,house,class) VALUES('Ritul',1,'Pant','11A');
5. Insert/Add a new field city into the table student.
ALTER TABLE student ADD COLUMN city VARCHAR(30); #changes table structure
...and update the city for all the existing rows e.g. as:
unoconv_2616343965.docx 1
,UPDATE student SET city='Delhi' WHERE roll=123; #changes data values
6. List/Display/show/print all records from the table student. ('*' means all columns) (If not using WHERE clause,
query picks up all the rows)
SELECT * FROM student; #* means all columns and NOT using WHERE means all rows
7. Change/update the class of a student.
UPDATE student SET class='12A' WHERE roll=1;
8. Shift all class 10A students to 11A.
UPDATE student SET class='11A' WHERE class='10A';
9. List the number of students coming from Delhi.
SELECT count(*) FROM student WHERE city='DELHI';
10. List the students coming from Delhi.
SELECT * FROM student WHERE city='DELHI';
11. List the student records if name starts with 'Kumar'.
SELECT * FROM student WHERE name LIKE 'Kumar%';
12. List the student records if name ends with 'Kumar'.
SELECT * FROM student WHERE name LIKE '%Kumar';
13. List the student records if name contains the word 'Kumar'.
SELECT * FROM student WHERE name LIKE '%Kumar%';
14. List the students who have joined after 1st of April 2018. DATE FORMAT => YYYYMMDD
SELECT name FROM student WHERE doj > 20180401;
15. List students if they have joined the school in 2015.
SELECT * FROM student WHERE YEAR(doj) = 2015;
16. List students if they have joined the school in April of 2015.
SELECT * FROM student WHERE MONTH(doj) = 4 AND YEAR(doj) = 2015;
17. List the classes in the school.
SELECT DISTINCT class FROM student;
18. Display the cities, the school has students from. (How many cities we have the students from?)
SELECT DISTINCT city FROM student;
19. Display the class 11A students.
SELECT * FROM student WHERE class='11A';
20. Display the class 11 students......Display the class 11A and 11C students.
SELECT * FROM student WHERE class='11A' AND OR class='11C';
OR
SELECT * FROM student WHERE class IN('11A','11C')
or
SELECT * FROM student WHERE class LIKE '11%';
21. Display the class 11A students of Tilak house.
SELECT *FROM student WHERE house='Tilak' AND class='11A';
22. Display the class 11A students of Tilak house if their marks are less than 40.
SELECT * FROM student WHERE house='Tilak' AND class='11A'
AND marks < 40;
marks between 40 and 60
23. Remove/delete the student record if class is 11C.
DELETE FROM student WHERE class='11C';
Remove/delete the student record if roll is 23.
DELETE FROM student WHERE roll=23;
24. Upgrade the class of all class 10 students.
UPDATE student SET class='11' WHERE class LIKE '10%';
25. Display the first 3 characters of each student name.
SELECT LEFT(name, 3) FROM student;
26. Display the last 3 characters of each student name.
SELECT RIGHT(name, 3) FROM student;
27. Display the number of characters in each student name.
SELECT name, LENGTH(name) FROM student;
28. List the students who have scored marks between 80 and 90.
SELECT * FROM student WHERE marks BETWEEN 80 AND 90;
or
SELECT * FROM student WHERE marks >=80 AND OR marks<=90;
29. Display the student names if 4th & 5th characters are 'JA'.
SELECT name FROM student WHERE MID(name,4,2)='JA';
30. Display the name if it contains at least 5 characters.
SELECT name FROM student WHERE LENGTH(name)>=5;
or
SELECT name FROM student WHERE name LIKE '_ _ _ _ _%'; WILD CARDS
31. Display the student records if name if lengthier than 20 characters.
SELECT * FROM student WHERE LENGTH(name)>20;
32. Round off the fee of each student. Also find the square root of fee.
SELECT ROUND(fee), SQRT(fee) FROM student;
33. Round off the fee of each student, to two places.
SELECT ROUND(fee,2) FROM student;
unoconv_2616343965.docx 2
, 34. Display the student records if their marks are even numbers.
SELECT * FROM student WHERE MOD(marks, 2)=0;
35. Drop the table student.
DROP TABLE student; USE IT WITH EXTRA CAUTION!!!
36. Increase the marks of every student by 10.
UPDATE student SET marks=marks+10;
37. Increase the marks of every student by 10% if it is less than 40.
UPDATE student SET marks=marks+marks*10/100 WHERE marks < 40;
38. Change the city name to New Delhi if it is Delhi.
UPDATE student SET city='New Delhi' WHERE city='Delhi';
39. Count the number of students.
SELECT COUNT(*) FROM student;
and
SELECT COUNT(name) FROM student; (this might give a different count!)
40. Count the number of students class-wise.
SELECT class, COUNT(class) FROM student GROUP BY class;
41. Display the table structure.
DESC student;
42. Display the tables in a database.
SHOW TABLES;
43. Delete all records of Delhi students.
DELETE FROM student WHERE city='Delhi';
DELETE FROM student will remove all the records, so be careful
44. Open the database named d123.
USE d123;
45. Display student names alphabetically.
SELECT name FROM student ORDER BY name;
46. Display student records house wise (in ascending order) and class wise (in descending order).
SELECT name FROM student ORDER BY house asc, class desc;
47. Remove the database d123.
DROP DATABASE d123;
48. Create a table s1 with roll and name where roll is a primary key.
CREATE TABLE s1(roll INT PRIMARY KEY, name VARCHAR(30));
49. Add the primary key constraint to the roll column of an existing table s2.
ALTER TABLE s2 ADD CONSTRAINT pk PRIMARY KEY (roll);
50. Remove the primary key constraint from the table s2.
ALTER TABLE s2 DROP PRIMARY KEY;
51. Create table s3 with following constraints - roll PK, name not null, age >0, folio unique, class default 10A.
CREATE TABLE s3(roll int PRIMARY KEY, name varchar(30) NOT NULL, age INT
CHECK(age>0), folio int UNIQUE, class VARCHAR(3) DEFAULT '10A');
AGGREGATE FUNCTIONS
Multiple Row Functions or Aggregate Functions operate on a set of rows to return a single value.
52. Find the highest and lowest marks scored in the database table and display it as 'Highest Marks' and 'Lowest Marks'.
SELECT MAX(marks) as 'Highest Marks', MIN(marks) as 'Lowest Marks' FROM student;
53. Find the average marks.
SELECT AVG(marks) as 'Average Marks' FROM student;
54. Find the total fee collected.
SELECT SUM(fee) as 'Total Fee' FROM student;
55. Find the number of students in the table.
SELECT COUNT(*) as 'No. of Students' FROM student;
56. Find the number of students in the table if their result has been declared.
SELECT COUNT(result) as 'No. of Results Generated' FROM student;
or
SELECT COUNT(*) as 'No. of Results Generated' FROM student WHERE result IS NOT NULL;
57. Find the number of students in the table whose result has not been declared.
SELECT COUNT(*) as 'No. of Results NOT Generated' FROM student WHERE result IS NULL;
QUERYING DATA USING GROUP BY, HAVING, ORDER BY
58. Find the number of students in each class.
SELECT class, COUNT(class) FROM student GROUP BY class;
59. Find the number of students in classes '6A', '7A', and '8A'.
(DON'T USE WHERE, INSTEAD USE HAVING TO CHECK A CONDITION WHILE USING GROUP BY)
SELECT class, COUNT(class) FROM student GROUP BY class HAVING class IN('6A','7A','8A');
60. Find the number of students in classes other than '6A', '7A', and '8A'.
SELECT class, COUNT(class) FROM student GROUP BY class HAVING class NOT IN('6A','7A','8A');
61. Display the student records class-wise.
SELECT * FROM student ORDER BY class;
62. Display the student records class-wise arranged alphabetically based on their names.
SELECT * FROM student ORDER BY class, name;
unoconv_2616343965.docx 3
