SQL Interview Questions
This page consists of SQL interview questions and answers.
SQL or Structured Query Language and we use it to add and modify data in a database.
A trigger is a piece of code that is associated with insert, update and delete query and gets executed whenever the associated query is executed on a particular table or view.
A stored procedure is a function that consists of a list of operations to perform database tasks.
A stored procedure can be called independently whereas, a trigger can't be executed directly but must be associated with some queries.
users
The SQL query to select the required columns is given below.
mysql> SELECT u.userid, u.firstname, u.lastname FROM user u WHERE u.userid = 'u10';
mysql> denotes MySQL command line.
mysql>
Assuming the score of the teams gets stored in the teamscore table having teamid and score column.
teamscore
teamid
score
So, to get the average score we will write the following query.
mysql> SELECT AVG(score) AS average_score FROM teamscore WHERE teamid = 12;
employeesalary
Lets say the employeesalary table has 2 columns employeeid and salary and the table has the following 5 entries.
employeeid
salary
mysql> SELECT es.employeeid, es.salary FROM employeesalary es ORDER BY es.salary DESC; +------------+------------+ | employeeid | salary | +------------+------------+ | e03 | 1500000.00 | | e05 | 1100000.00 | | e01 | 1000000.00 | | e04 | 900000.00 | | e02 | 700000.00 | +------------+------------+
We have already sorted the data in descending order using ORDER BY now, we will get the 2nd highest salary using LIMIT.
ORDER BY
LIMIT
mysql> SELECT es.employeeid, es.salary FROM employeesalary es ORDER BY es.salary DESC LIMIT 1,1; +------------+------------+ | employeeid | salary | +------------+------------+ | e05 | 1100000.00 | +------------+------------+
LIMIT 1,1 means we want 1 row and offset is 1 so, the second row will be selected which holds the 2nd highest salary.
LIMIT 1,1
examscore
Lets say the examscore table has two columns namely studentid and score. To get the first 10 highest marks we have to sort the data in descending order and them limit our SELECT query to 10 rows.
studentid
mysql> SELECT es.studentid, es.score FROM examscore es ORDER BY es.score DESC LIMIT 10;
employee
Lets say the employee table has 4 columns employeeid, firstname, lastname and created_at.
firstname
lastname
created_at
Lets say the table has the following entries.
mysql> SELECT employeeid, firstname, lastname FROM employee ORDER BY created_at; +------------+-----------+----------+ | employeeid | firstname | lastname | +------------+-----------+----------+ | e01 | John | Doe | | e02 | Jane | Doe | | e03 | Peter | Parker | | e04 | Bruce | Banner | | e05 | Bruce | Wayne | +------------+-----------+----------+
So, the query to select the last three entries will be the following.
mysql> SELECT * FROM ( SELECT employeeid, firstname, lastname, created_at FROM employee ORDER BY employeeid DESC LIMIT 3 ) tbl ORDER BY tbl.employeeid ASC; +------------+-----------+----------+---------------------+ | employeeid | firstname | lastname | created_at | +------------+-----------+----------+---------------------+ | e03 | Peter | Parker | 2018-01-02 15:16:17 | | e04 | Bruce | Banner | 2018-01-03 10:20:30 | | e05 | Bruce | Wayne | 2018-01-04 12:00:00 | +------------+-----------+----------+---------------------+
mysql> SELECT (10 + 20) AS sum; +-----+ | sum | +-----+ | 30 | +-----+