Master all SQL Queries for interviews
Written on July 28, 2024
Views : Loading...
- Query to show databases:
Input:
mysql>show databases;
Output:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
- Query to create database.
mysql> create database demo;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
Observation: demo database has been added
- Query to use a database.
mysql> use demo;
Database changed
- Query to create a table.
mysql> create table student (name varchar(30), id int not null primary key, address varchar(50), marks int);
Query OK, 0 rows affected (0.04 sec)
- Query to get information about a table
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(30) | YES | | NULL | |
| id | int | NO | PRI | NULL | |
| address | varchar(50) | YES | | NULL | |
| marks | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- Query to insert a record in a table.
mysql> insert into student(marks,id,name,address) values (78,12,"Rishabh","Cleo County, Noida");
Query OK, 1 row affected (0.04 sec)
mysql> insert into student values ('Keshav', 69, 'faridabad, delhi',15);
Query OK, 1 row affected (0.05 sec)
mysql> insert into student values('Ram',45,'rishikesh',79), ('Ravi',17,'Delhi',90);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
- Query to print the table.
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ravi | 17 | Delhi | 90 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | faridabad, delhi | 15 |
+---------+----+--------------------+-------+
4 rows in set (0.00 sec)
mysql> select name from student;
+---------+
| name |
+---------+
| Rishabh |
| Ravi |
| Ram |
| Keshav |
+---------+
4 rows in set (0.00 sec)
mysql> select name, id from student;
+---------+----+
| name | id |
+---------+----+
| Rishabh | 12 |
| Ravi | 17 |
| Ram | 45 |
| Keshav | 69 |
+---------+----+
4 rows in set (0.01 sec)
mysql> select * from student where id=12;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
+---------+----+--------------------+-------+
1 row in set (0.01 sec)
- Updating an entry:
mysql> update student set address='Dehradun' where id=69;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ravi | 17 | Delhi | 90 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
4 rows in set (0.00 sec)
- Adding a new column to a table
mysql> alter table student add phoneNo int;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from student;
+---------+----+--------------------+-------+---------+
| name | id | address | marks | phoneNo |
+---------+----+--------------------+-------+---------+
| Rishabh | 12 | Cleo County, Noida | 78 | NULL |
| Ravi | 17 | Delhi | 90 | NULL |
| Ram | 45 | rishikesh | 79 | NULL |
| Keshav | 69 | Dehradun | 15 | NULL |
+---------+----+--------------------+-------+---------+
4 rows in set (0.00 sec)
mysql> update student set phoneNo=123344;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> select * from student;
+---------+----+--------------------+-------+---------+
| name | id | address | marks | phoneNo |
+---------+----+--------------------+-------+---------+
| Rishabh | 12 | Cleo County, Noida | 78 | 123344 |
| Ravi | 17 | Delhi | 90 | 123344 |
| Ram | 45 | rishikesh | 79 | 123344 |
| Keshav | 69 | Dehradun | 15 | 123344 |
+---------+----+--------------------+-------+---------+
4 rows in set (0.00 sec)
- Changing a datatype of a table column
mysql> alter table student modify column name varchar(60);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(60) | YES | | NULL | |
| id | int | NO | PRI | NULL | |
| address | varchar(50) | YES | | NULL | |
| marks | int | YES | | NULL | |
| phoneNo | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
- Query to delete a column from a table.
mysql> alter table student drop column phoneNo;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(60) | YES | | NULL | |
| id | int | NO | PRI | NULL | |
| address | varchar(50) | YES | | NULL | |
| marks | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
- Deleting a record.
mysql> delete from student where name='Ravi';
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
3 rows in set (0.01 sec)
- Query sum, avg, count, max, min functions
mysql> select sum(marks) from student;
+------------+
| sum(marks) |
+------------+
| 172 |
+------------+
1 row in set (0.00 sec)
mysql> select avg(marks) from student;
+------------+
| avg(marks) |
+------------+
| 57.3333 |
+------------+
1 row in set (0.01 sec)
mysql> select count(name) from student;
+-------------+
| count(name) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
mysql> select max(marks) from student;
+------------+
| max(marks) |
+------------+
| 79 |
+------------+
1 row in set (0.01 sec)
mysql> select min(marks) from student;
+------------+
| min(marks) |
+------------+
| 15 |
+------------+
1 row in set (0.00 sec)
- Query to display in ascending or descending order.
mysql> select * from student order by id;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
3 rows in set (0.01 sec)
mysql> select * from student order by id desc;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Keshav | 69 | Dehradun | 15 |
| Ram | 45 | rishikesh | 79 |
| Rishabh | 12 | Cleo County, Noida | 78 |
+---------+----+--------------------+-------+
3 rows in set (0.01 sec)
- Like query.
mysql> select * from student where name like 'R%';
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
+---------+----+--------------------+-------+
2 rows in set (0.01 sec)
mysql> select * from student where name like '%h';
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
+---------+----+--------------------+-------+
1 row in set (0.01 sec)
mysql> select * from student where name like '_i%';
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
+---------+----+--------------------+-------+
1 row in set (0.01 sec)
mysql> select * from student where name like '%b_';
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
+---------+----+--------------------+-------+
1 row in set (0.01 sec)
Creating a new table
mysql> create table employee(id int not null primary key, salary int, empcode int, name varchar(30));
Query OK, 0 rows affected (0.03 sec)
mysql> insert into employee values(12,20000,102,'aman'),(23,6000,104,'arun'),(78,7000,105,'ram'),(45,30000,202,'Shyam');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from employee;
+----+--------+---------+-------+
| id | salary | empcode | name |
+----+--------+---------+-------+
| 12 | 20000 | 102 | aman |
| 23 | 6000 | 104 | arun |
| 45 | 30000 | 202 | Shyam |
| 78 | 7000 | 105 | ram |
+----+--------+---------+-------+
4 rows in set (0.00 sec)
Join queries
Inner Join
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from employee;
+----+--------+---------+-------+
| id | salary | empcode | name |
+----+--------+---------+-------+
| 12 | 20000 | 102 | aman |
| 23 | 6000 | 104 | arun |
| 45 | 30000 | 202 | Shyam |
| 78 | 7000 | 105 | ram |
+----+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from student inner join employee on student.id=employee.id;
+---------+----+--------------------+-------+----+--------+---------+-------+
| name | id | address | marks | id | salary | empcode | name |
+---------+----+--------------------+-------+----+--------+---------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 | 12 | 20000 | 102 | aman |
| Ram | 45 | rishikesh | 79 | 45 | 30000 | 202 | Shyam |
+---------+----+--------------------+-------+----+--------+---------+-------+
2 rows in set (0.00 sec)
Left join
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from employee;
+----+--------+---------+-------+
| id | salary | empcode | name |
+----+--------+---------+-------+
| 12 | 20000 | 102 | aman |
| 23 | 6000 | 104 | arun |
| 45 | 30000 | 202 | Shyam |
| 78 | 7000 | 105 | ram |
+----+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from student left join employee on student.id=employee.id;
+---------+----+--------------------+-------+------+--------+---------+-------+
| name | id | address | marks | id | salary | empcode | name |
+---------+----+--------------------+-------+------+--------+---------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 | 12 | 20000 | 102 | aman |
| Ram | 45 | rishikesh | 79 | 45 | 30000 | 202 | Shyam |
| Keshav | 69 | Dehradun | 15 | NULL | NULL | NULL | NULL |
+---------+----+--------------------+-------+------+--------+---------+-------+
3 rows in set (0.00 sec)
Right Join
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from employee;
+----+--------+---------+-------+
| id | salary | empcode | name |
+----+--------+---------+-------+
| 12 | 20000 | 102 | aman |
| 23 | 6000 | 104 | arun |
| 45 | 30000 | 202 | Shyam |
| 78 | 7000 | 105 | ram |
+----+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from student right join employee on stu
dent.id=employee.id;
+---------+------+--------------------+-------+----+--------+---------+-------+
| name | id | address | marks | id | salary | empcode | name |
+---------+------+--------------------+-------+----+--------+---------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 | 12 | 20000 | 102 | aman |
| NULL | NULL | NULL | NULL | 23 | 6000 | 104 | arun |
| Ram | 45 | rishikesh | 79 | 45 | 30000 | 202 | Shyam |
| NULL | NULL | NULL | NULL | 78 | 7000 | 105 | ram |
+---------+------+--------------------+-------+----+--------+---------+-------+
4 rows in set (0.01 sec)
Cross Join
mysql> select * from student;
+---------+----+--------------------+-------+
| name | id | address | marks |
+---------+----+--------------------+-------+
| Rishabh | 12 | Cleo County, Noida | 78 |
| Ram | 45 | rishikesh | 79 |
| Keshav | 69 | Dehradun | 15 |
+---------+----+--------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from employee;
+----+--------+---------+-------+
| id | salary | empcode | name |
+----+--------+---------+-------+
| 12 | 20000 | 102 | aman |
| 23 | 6000 | 104 | arun |
| 45 | 30000 | 202 | Shyam |
| 78 | 7000 | 105 | ram |
+----+--------+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from student cross join employee;
+---------+----+--------------------+-------+----+--------+---------+-------+
| name | id | address | marks | id | salary | empcode | name |
+---------+----+--------------------+-------+----+--------+---------+-------+
| Keshav | 69 | Dehradun | 15 | 12 | 20000 | 102 | aman |
| Ram | 45 | rishikesh | 79 | 12 | 20000 | 102 | aman |
| Rishabh | 12 | Cleo County, Noida | 78 | 12 | 20000 | 102 | aman |
| Keshav | 69 | Dehradun | 15 | 23 | 6000 | 104 | arun |
| Ram | 45 | rishikesh | 79 | 23 | 6000 | 104 | arun |
| Rishabh | 12 | Cleo County, Noida | 78 | 23 | 6000 | 104 | arun |
| Keshav | 69 | Dehradun | 15 | 45 | 30000 | 202 | Shyam |
| Ram | 45 | rishikesh | 79 | 45 | 30000 | 202 | Shyam |
| Rishabh | 12 | Cleo County, Noida | 78 | 45 | 30000 | 202 | Shyam |
| Keshav | 69 | Dehradun | 15 | 78 | 7000 | 105 | ram |
| Ram | 45 | rishikesh | 79 | 78 | 7000 | 105 | ram |
| Rishabh | 12 | Cleo County, Noida | 78 | 78 | 7000 | 105 | ram |
+---------+----+--------------------+-------+----+--------+---------+-------+
12 rows in set (0.00 sec)
Share this blog
Related Posts
Unveiling 2-Edge Connected Components with DFS Lowlinking
25-11-2024
CP & Interviews
Graph Theory
Algorithms
Bridges
In the realm of graph theory, understanding the connectivity of a network is crucial for numerous ap...
Z Algorithm and Implementation for String Searching
24-11-2024
CP & Interviews
String
Dive deep into the Z Algorithm, a fundamental technique in string processing. This blog elucidates t...
Analysis of Popular Sorting Algorithms
04-11-2023
CP & Interviews
Bubble Sort
Quick Sort
Insertion Sort
Selection Sort
Merge Sort
Radix Sort
Count Sort
Bucket Sort
Shell Sorting
DNF Sort
Pancake Sort
Tim Sort
Explore the most efficient Sorting Algorithms for Competitive Programming and Interviews. Learn abou...
The Problem of Dividing Balls into Boxes
23-07-2023
CP & Interviews
Maths
PnC
This document discusses various variants of the problem of dividing n balls into *m* boxes. Markdown...
Master Linear Dynamic Programming
22-07-2023
CP & Interviews
Dynamic Programming
Linear DP
Leetcode Problems
Enhance your dynamic programming skills with our comprehensive collection of Linear DP problems from...
Master Tree Algorithms: Solutions for CSES and Other Problem Sets
19-07-2023
CP & Interviews
Tree Algorithms
CSES
Become a Tree Algorithms expert with our comprehensive solutions for CSES and various problem sets. ...
Solutions for CSES Graph Problems | CSES Problem Set Guide
17-07-2023
CP & Interviews
CSES
Graph
Explore efficient solutions and master essential algorithms for CSES Graph problems in the CSES Prob...
Solutions for CSES Sorting and Searching Problems | CSES Problem Set Guide
15-07-2023
CP & Interviews
CSES
Sorting
Searching
Explore efficient solutions to the Sorting and Searching section in the CSES Problem Set. Master ess...
Dynamic Programming Solutions for CSES and Other Problem Sets
12-07-2023
CP & Interviews
Dynamic Programming
CSES
Explore efficient solutions to dynamic programming problems from the CSES problem set and other repu...