MySQL Tutorial

To create the database named 'School' we can use the following SQL syntax:

CREATE DATABASE database_name;

So we will execute the statement:

CREATE DATABASE School;

To check whether the database is created or not from the list of databases, we can execute the following SQL statement:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| School             |
+--------------------+
        

Yeah!,now there exists our newly created database name 'School'.

Now we will create our tables to this database one by one.

Before going to execute SQL statements for table creation, first we need to get into the database or select the database from the list of avaiable databases where we want to create those tables. For that, we can execute the following SQL syntax:

USE database_name;

So we will execute the statement:

USE School;

To create the table named 'Course' we can use the following SQL syntax:

CREATE TABLE table_name(column_name1 datatype(length) constraints, column_name2 datatype(length) constraints,............., column_nameN datatype(length) constraints);

So we will execute the statement:

CREATE TABLE Course(cid INT PRIMARY KEY, course_name VARCHAR(10));

OK after executing the above query, we will no check whether the table is actually created or not using the following SQL statement:

SHOW TABLES;

+------------------+
| Tables_in_School |
+------------------+
| Course           |
+------------------+

Yeah, its created.

Now let's create rest of the tables.

The tables 'Teacher' and 'Student' contains one or more FOREIGN KEY attributes(columns). 'Student' table contains foreign key attribute -> cid and tid. Also, 'Teacher' table contains foreign key attribute -> cid.

Therefore, to create tables containing foreign key constraint attributes, we can use the following SQL syntax:

CREATE TABLE table_name(column_name1 datatype(length) constraints, column_name2 datatype(length) constraints,............., column_nameN datatype(length) constraints, foreign_column_name1 datatype, foreign_column_name2 datatype, .............., foreign_column_nameN datatype, FOREIGN KEY(foreign_column1) REFERENCES reference_table_name(foreign_column1), FOREIGN KEY(foreign_column2) REFERENCES reference_table_name(foreign_column2), .............., FOREIGN KEY(foreign_columnN) REFERENCES reference_table_name(foreign_columnN));

To create table named 'Teacher' we can execute the following SQL statement:

CREATE TABLE Teacher(tid INT PRIMARY KEY, fname VARCHAR(10) NOT NULL, lname VARCHAR(10), gender VARCHAR(6), address VARCHAR(25), cid INT, FOREIGN KEY(cid) REFERENCES Course(cid));

To create table named 'Student' we can execute the following SQL statement:

CREATE TABLE Student(sid INT PRIMARY KEY, fname VARCHAR(10) NOT NULL, lname VARCHAR(10), gender VARCHAR(6), address VARCHAR(25), class VARCHAR(2), tid INT, cid INT, FOREIGN KEY(tid) REFERENCES Teacher(tid), FOREIGN KEY(cid) REFERENCES Course(cid));

Let's check now.

SHOW TABLES;

+------------------+
| Tables_in_School |
+------------------+
| Course           |
| Student          |
| Teacher          |
+------------------+

yeah, all the tables are successfully created.

Now if want to check whether the table is created with correct datatypes, length and constraints we can use the following syntax:

DESCRIBE table_name;

So, let's execute one by one.

DESCRIBE Student;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| sid     | int(11)     | NO   | PRI | NULL    |       |
| fname   | varchar(10) | NO   |     | NULL    |       |
| lname   | varchar(10) | YES  |     | NULL    |       |
| gender  | varchar(6)  | YES  |     | NULL    |       |
| address | varchar(25) | YES  |     | NULL    |       |
| class   | varchar(2)  | YES  |     | NULL    |       |
| tid     | int(11)     | YES  | MUL | NULL    |       |
| cid     | int(11)     | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+

DESCRIBE Course;

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cid         | int(11)     | NO   | PRI | NULL    |       |
| course_name | varchar(10) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

DESCRIBE Teacher;

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| tid     | int(11)     | NO   | PRI | NULL    |       |
| fname   | varchar(10) | NO   |     | NULL    |       |
| lname   | varchar(10) | YES  |     | NULL    |       |
| gender  | varchar(6)  | YES  |     | NULL    |       |
| address | varchar(25) | YES  |     | NULL    |       |
| cid     | int(11)     | YES  | MUL | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Yeah, it's now clear that the tables are created correcly.

We will now insert the following sample data into the tables.

Table: Course

Table: Teacher

Table: Student

To insert data into table, we can use the following SQL syntax:

INTO INTO table_name(column1, column2, ......, columnN) VALUES(value1, value2, ........, valueN);

To insert above data into 'Course' table, we will execute the statement:

INSERT INTO Course(cid, course_name) VALUES(101, 'CS');
INSERT INTO Course(cid, course_name) VALUES(102, 'Humanities');
INSERT INTO Course(cid, course_name) VALUES(103, 'Bio-Maths');

To insert above data into 'Teacher' table, we will execute the statement:

INSERT INTO Teacher(tid, fname, lname, gender, address, cid) VALUES(1001, 'Rajeev', 'Ravi', 'male', 'Akshaya Nagar', 102);
INSERT INTO Teacher(tid, fname, lname, gender, address, cid) VALUES(1002, 'Amaana', 'Ameer', 'female', 'Akshara Nagar', 101);
INSERT INTO Teacher(tid, fname, lname, gender, address, cid) VALUES(1003, 'Haritha', 'Hari', 'female', 'Gandhi Street', 103);

To insert above data into 'Student' table, we will execute the statement:

INSERT INTO Student(sid, fname, lname, gender, address, class, tid, cid) VALUES(1, 'Manu', 'Manoj', 'male', 'Sindhu Nagar', 11, 1002, 101);
INSERT INTO Student(sid, fname, lname, gender, address, class, tid, cid) VALUES(2, 'Mini', NULL, 'female', 'Electronics street', 12, 1002, 101);
INSERT INTO Student(sid, fname, lname, gender, address, class, tid, cid) VALUES(3, 'Geevan', 'Geeva', 'male', 'Electronics street', 12, 1003, 103);
INSERT INTO Student(sid, fname, lname, gender, address, class, tid, cid) VALUES(4, 'Madhav', NULL, 'male', 'Bhagya Nagar', 11, 1003, 103);
INSERT INTO Student(sid, fname, lname, gender, address, class, tid, cid) VALUES(5, 'Maalu', 'Nidhi', 'female', 'Bhagya Nagar', 11, 1001, 102);

Data insertion is completed. Let's check whether the data is inserted or not by using 'SELECT' command.

Syntax: 

SELECT column_name1, column_name2, ......, columnN FROM table_name WHERE condition;
or
SELECT * FROM table_name WHERE condition;

"*" is used if you want to select all columns.

We will execute the following MYSQL select queries to view the data inserted in each table.

SELECT * FROM Course;
+-----+-------------+
| cid | course_name |
+-----+-------------+
| 101 | CS          |
| 102 | Humanities  |
| 103 | Bio-Maths   |
+-----+-------------+

SELECT * FROM Teacher;
+------+---------+-------+--------+---------------+------+
| tid  | fname   | lname | gender | address       | cid  |
+------+---------+-------+--------+---------------+------+
| 1001 | Rajeev  | Ravi  | male   | Akshaya Nagar |  102 |
| 1002 | Amaana  | Ameer | female | Akshara Nagar |  101 |
| 1003 | Haritha | Hari  | female | Gandhi Street |  103 |
+------+---------+-------+--------+---------------+------+

SELECT * FROM Student;
+-----+---------+-------+--------+--------------------+-------+------+------+
| sid | fname   | lname | gender | address            | class | tid  | cid  |
+-----+---------+-------+--------+--------------------+-------+------+------+
|   1 | Manu    | Manoj | male   | Sindhu Nagar       | 11    | 1002 |  101 |
|   2 | Mini    | NULL  | female | Manohar Nagar      | 12    | 1002 |  101 |
|   3 | Geevan  | Geeva | male   | Electronics street | 12    | 1003 |  103 |
|   4 | Madhav  | NULL  | male   | Bhagya Nagar       | 11    | 1003 |  103 |
|   5 | Maalu   | Nidhi | female | Bhagya Nagar       | 11    | 1001 |  102 |
+-----+---------+-------+--------+--------------------+-------+------+------+

Now, it's clear that all data is inserted correcly.

The questions are given below. We need to write appropriate SQL SELECT queries for answering the following questions.

1. How many students are joined?
2. How many male students joined?
3. How many female students joined?
4. Show the firstname and lastname of all male students.
5. Show the firstname and lastname of all female students.
6. Show the sid of all students studying in class 12th.
7. Show all the details from 'Student'table whose sid less than 4.
8. Show all the details from 'Student'table whose sid between 2 and 4.
9. Show the sid of all students whose lname is null or blank.
10. Show the sid and fname of all students whose lname is not null(blank). 
11. Select firstname and lastname of all students whose firstname starts with M.
12. Select firstname and lastname of all students whose firstname not starts with M.
13. Select firstname and lastname of all students whose firstname starts with M and ends with U.
14. Select firstname and lastname of all students whose firstname starts with M and ends with NI.
15. Select firstname and lastname of all students whose firstname's second letter is I.
16. Select firstname and lastname of all students whose lastname ends with I.
17. Select firstname and lastname of all students whose firstname's third letter is N.
18. Select firstname and lastname of all students whose firstname's third letter is N and lastname not null or empty.
19. Select firstname and class of all students who are coming from Bhagya Nagar or Electronics street.
20. Select all details from 'Student' table whose gender is male and coming from Bhagya Nagar or Electronics street.
21. Select sid, firstname, lastname, class and course enrolled of all the students.
22. Select tid, firstname, lastname, teaching subject/course of all the teachers.

1. How many students are joined?

SELECT COUNT(sid) from Student;
+------------+
| COUNT(sid) |
+------------+
|          5 |
+------------+

2. How many male students joined?

SELECT COUNT(sid) FROM Student WHERE gender = 'male';
+------------+
| COUNT(sid) |
+------------+
|          3 |
+------------+

3. How many female students joined?

SELECT COUNT(sid) FROM Student WHERE gender = 'female';
+------------+
| COUNT(sid) |
+------------+
|          2 |
+------------+

4. Show the firstname and lastname of all male students.

SELECT fname, lname FROM Student WHERE gender = 'male';
+---------+-------+
| fname   | lname |
+---------+-------+
| Manu    | Manoj |
| Geevan  | Geeva |
| Madhav  | NULL  |
+---------+-------+

5. Show the firstname and lastname of all female students.

SELECT fname, lname FROM Student WHERE gender = 'female';
+-------+-------+
| fname | lname |
+-------+-------+
| Mini  | NULL  |
| Maalu | Nidhi |
+-------+-------+

6. Show the sid of all students studying in class 12th.

SELECT sid from Student WHERE class = 12;
+-----+
| sid |
+-----+
|   2 |
|   3 |
+-----+

7. Show all the details from 'Student'table whose sid less than 4.

SELECT * FROM Student WHERE sid < 4;
+-----+--------+-------+--------+--------------------+-------+------+------+
| sid | fname  | lname | gender | address            | class | tid  | cid  |
+-----+--------+-------+--------+--------------------+-------+------+------+
|   1 | Manu   | Manoj | male   | Sindhu Nagar       | 11    | 1002 |  101 |
|   2 | Mini   | NULL  | female | Electronics street | 12    | 1002 |  101 |
|   3 | Geevan | Geeva | male   | Electronics street | 12    | 1003 |  103 |
+-----+--------+-------+--------+--------------------+-------+------+------+

8. Show all the details from 'Student'table whose sid between 2 and 4. [2 and 4 are inclusive]

SELECT * FROM Student WHERE sid BETWEEN 2 and 4;
+-----+--------+-------+--------+--------------------+-------+------+------+
| sid | fname  | lname | gender | address            | class | tid  | cid  |
+-----+--------+-------+--------+--------------------+-------+------+------+
|   2 | Mini   | NULL  | female | Electronics street | 12    | 1002 |  101 |
|   3 | Geevan | Geeva | male   | Electronics street | 12    | 1003 |  103 |
|   4 | Madhav | NULL  | male   | Bhagya Nagar       | 11    | 1003 |  103 |
+-----+--------+-------+--------+--------------------+-------+------+------+

9. Show the sid of all students whose lname is null or blank.

SELECT sid FROM Student WHERE lname IS NULL;
+-----+
| sid |
+-----+
|   2 |
|   4 |
+-----+

10. Show the sid and fname of all students whose lname is not null(blank).

SELECT sid, fname FROM Student WHERE lname IS NOT NULL;
+-----+--------+
| sid | fname  |
+-----+--------+
|   1 | Manu   |
|   3 | Geevan |
|   5 | Maalu  |
+-----+--------+

11. Select firstname and lastname of all students whose firstname starts with M.

SELECT fname, lname FROM Student WHERE fname LIKE 'M%';
+--------+-------+
| fname  | lname |
+--------+-------+
| Manu   | Manoj |
| Mini   | NULL  |
| Madhav | NULL  |
| Maalu  | Nidhi |
+--------+-------+

12. Select firstname and lastname of all students whose firstname not starts with M.

SELECT fname, lname FROM Student WHERE fname NOT LIKE 'M%';
+--------+-------+
| fname  | lname |
+--------+-------+
| Geevan | Geeva |
+--------+-------+

13. Select firstname and lastname of all students whose firstname starts with M and ends with U.

SELECT fname, lname FROM Student WHERE fname LIKE 'M%U';
+-------+-------+
| fname | lname |
+-------+-------+
| Manu  | Manoj |
| Maalu | Nidhi |
+-------+-------+

14. Select firstname and lastname of all students whose firstname starts with M and ends with NI.

SELECT fname, lname FROM Student WHERE fname LIKE 'M%NI';
+-------+-------+
| fname | lname |
+-------+-------+
| Mini  | NULL  |
+-------+-------+

15. Select firstname and lastname of all students whose firstname's second letter is I.

SELECT fname, lname FROM Student WHERE fname LIKE '_I%';
+-------+-------+
| fname | lname |
+-------+-------+
| Mini  | NULL  |
+-------+-------+

16. Select firstname and lastname of all students whose lastname ends with I.

SELECT fname, lname FROM Student WHERE lname LIKE '%I';
+-------+-------+
| fname | lname |
+-------+-------+
| Maalu | Nidhi |
+-------+-------+

17. Select firstname and lastname of all students whose firstname's third letter is N.

SELECT fname, lname FROM Student WHERE fname LIKE '__N%';
+-------+-------+
| fname | lname |
+-------+-------+
| Manu  | Manoj |
| Mini  | NULL  |
+-------+-------+

18. Select firstname and lastname of all students whose firstname's third letter is N and lastname not null or empty.

SELECT fname, lname FROM Student WHERE fname LIKE '__N%' and lname IS NOT NULL;
+-------+-------+
| fname | lname |
+-------+-------+
| Manu  | Manoj |
+-------+-------+

19. Select firstname and class of all students who are coming from Bhagya Nagar or Electronics street.

SELECT fname, class FROM Student WHERE address = 'Bhagya Nagar' or address = 'Electronics street';
+--------+-------+
| fname  | class |
+--------+-------+
| Mini   | 12    |
| Geevan | 12    |
| Madhav | 11    |
| Maalu  | 11    |
+--------+-------+

20. Select all details from 'Student' table whose gender is male and coming from Bhagya Nagar or Electronics street.

SELECT * FROM Student WHERE gender = 'male' and (address = 'Bhagya Nagar' or address = 'Electronics street');
+-----+--------+-------+--------+--------------------+-------+------+------+
| sid | fname  | lname | gender | address            | class | tid  | cid  |
+-----+--------+-------+--------+--------------------+-------+------+------+
|   3 | Geevan | Geeva | male   | Electronics street | 12    | 1003 |  103 |
|   4 | Madhav | NULL  | male   | Bhagya Nagar       | 11    | 1003 |  103 |
+-----+--------+-------+--------+--------------------+-------+------+------+

21. Select sid, firstname, lastname, class and course enrolled of all the students.

SELECT Student.sid, Student.fname, Student.lname, Student.class, Course.course_name FROM Student INNER JOIN Course ON Student.cid = Course.cid;
+-----+--------+-------+-------+-------------+
| sid | fname  | lname | class | course_name |
+-----+--------+-------+-------+-------------+
|   1 | Manu   | Manoj | 11    | CS          |
|   2 | Mini   | NULL  | 12    | CS          |
|   3 | Geevan | Geeva | 12    | Bio-Maths   |
|   4 | Madhav | NULL  | 11    | Bio-Maths   |
|   5 | Maalu  | Nidhi | 11    | Humanities  |
+-----+--------+-------+-------+-------------+

22. Select tid, firstname, lastname, teaching subject/course of all the teachers.

SELECT Teacher.tid, Teacher.fname, Teacher.lname, Course.course_name FROM Teacher INNER JOIN Course ON Teacher.cid = Course.cid;
+------+---------+-------+-------------+
| tid  | fname   | lname | course_name |
+------+---------+-------+-------------+
| 1002 | Amaana  | Ameer | CS          |
| 1001 | Rajeev  | Ravi  | Humanities  |
| 1003 | Haritha | Hari  | Bio-Maths   |
+------+---------+-------+-------------+

The requirements are given below. We need to write appropriate SQL ALTER queries for executing the following requirements.

23. Change column named 'address' to 'location' of 'Student' table.
24. Add NOT NULL constraint to the existing column named 'class' of table 'Student' so that to make the column named 'class' should not 
be blank/null.
25. Change datatype of column named 'class' from VARCHAR(2) to integer.
26. Add new column named 'passout' to 'Student' table with data type varchar and length 3.

23. Change column named 'address' to 'location' of 'Student' table.

ALTER TABLE Student CHANGE COLUMN address location VARCHAR(25);

24. Add NOT NULL constraint to the existing column named 'class' of table 'Student' so that to make the column named 'class' should not be blank/null.

ALTER TABLE Student MODIFY class VARCHAR(2) NOT NULL;

25. Change datatype of column named 'class' from VARCHAR(2) to integer.

ALTER TABLE Student MODIFY class INT;

describe Student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| sid      | int(11)     | NO   | PRI | NULL    |       |
| fname    | varchar(10) | NO   |     | NULL    |       |
| lname    | varchar(10) | YES  |     | NULL    |       |
| gender   | varchar(6)  | YES  |     | NULL    |       |
| location | varchar(25) | YES  |     | NULL    |       |
| class    | int(11)     | YES  |     | NULL    |       |
| tid      | int(11)     | YES  | MUL | NULL    |       |
| cid      | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+

26. Add new column named 'passout' to 'Student' table with data type varchar and length 3 with value 'no' as default constraint.

ALTER TABLE Student ADD passout VARCHAR(3) DEFAULT 'no';

select * from Student;
+-----+--------+-------+--------+--------------------+-------+------+------+---------+
| sid | fname  | lname | gender | location           | class | tid  | cid  | passout |
+-----+--------+-------+--------+--------------------+-------+------+------+---------+
|   1 | Manu   | Manoj | male   | Sindhu Nagar       |    11 | 1002 |  101 | no      |
|   2 | Mini   | NULL  | female | Electronics street |    12 | 1002 |  101 | no      |
|   3 | Geevan | Geeva | male   | Electronics street |    12 | 1003 |  103 | no      |
|   4 | Madhav | NULL  | male   | Bhagya Nagar       |    11 | 1003 |  103 | no      |
|   5 | Maalu  | Nidhi | female | Bhagya Nagar       |    11 | 1001 |  102 | no      |
+-----+--------+-------+--------+--------------------+-------+------+------+---------+

The requirements are given below. We need to write appropriate SQL ALTER queries for executing the following requirements.

27. Update location of student with sid '1' from 'Sindhu Nagar' to 'Yamuna Nagar';
28. Update class of student with sid '4' from '11' to '12'.
29. Update passout status of all students studying in class 12th from 'no' to 'yes'.

27. Update location of student with sid '1' from 'Sindhu Nagar' to 'Yamuna Nagar';

UPDATE Student SET location = 'Yamuna Nagar' WHERE sid = 1;

28. Update class of student with sid '4' from '11' to '12'.

UPDATE Student SET class = 12 WHERE sid = 4;

29. Update passout status of all students studying in class 12th from 'no' to 'yes'.

UPDATE Student SET passout = 'yes' WHERE class = 12;

SELECT * FROM Student;
+-----+--------+-------+--------+--------------------+-------+------+------+---------+
| sid | fname  | lname | gender | location           | class | tid  | cid  | passout |
+-----+--------+-------+--------+--------------------+-------+------+------+---------+
|   1 | Manu   | Manoj | male   | Yamuna Nagar       |    11 | 1002 |  101 | no      |
|   2 | Mini   | NULL  | female | Electronics street |    12 | 1002 |  101 | yes     |
|   3 | Geevan | Geeva | male   | Electronics street |    12 | 1003 |  103 | yes     |
|   4 | Madhav | NULL  | male   | Bhagya Nagar       |    12 | 1003 |  103 | yes     |
|   5 | Maalu  | Nidhi | female | Bhagya Nagar       |    11 | 1001 |  102 | no      |
+-----+--------+-------+--------+--------------------+-------+------+------+---------+

So now we are asked to delete all the 12th passout students from the 'Student' table.

DELETE FROM Student WHERE class = 12 AND passout = 'yes';

SELECT * FROM Student;
+-----+-------+-------+--------+--------------+-------+------+------+---------+
| sid | fname | lname | gender | location     | class | tid  | cid  | passout |
+-----+-------+-------+--------+--------------+-------+------+------+---------+
|   1 | Manu  | Manoj | male   | Yamuna Nagar |    11 | 1002 |  101 | no      |
|   5 | Maalu | Nidhi | female | Bhagya Nagar |    11 | 1001 |  102 | no      |
+-----+-------+-------+--------+--------------+-------+------+------+---------+

DELETE FROM Student;

TRUNCATE TABLE Student;

DROP TABLE Student;

DROP DATABASE School;