SQL – Interview Questions

Database is an organized collection of data stored electronically.

A database management system(DBMS) is a software which is used to manage database. It allows to perform a number of operation(CURD) on database while considering appropriate security measures.

A relational database is a collection of data items with pre-defined relationships between them. A relational database is based on relational model.

Relational Modal organizes data into two or more tables. Each table has set of columns and rows with a unique key identifying each row.

Rows are called records or tuples. Columns are called attributes.

Example: MySQL, PostgresSQL, MariaDB, Microsoft SQL Server.

Structured Query Language is used to write queries for manipulation to the database. It is used to manage RDBMS and used to perform various operations(CRUD) etc. on it.

NoSQL systems use JSON format to store data.

Example : MongoDB, CoucheDB etc

NoSQL databases are a great fit for many modern applications such as mobile, web, and gaming that require flexible, scalable, high-performance, and highly functional databases.

No, SQL is not case sensitive language. But often SQL syntax are written in uppercase.

Select everything from a table name books

SELECT * FROM `books`;

clause are used to filter result from the database

SQL SELECT AS alias is used to assign temporary names to a table or column.

SELECT column_name1 AS alias_name1, column_name2 AS alias_name2, ... FROM table_name; 

Using DISTINCT keyword.

For example to select unique name from student table

SELECT DISTINCT student_name FROM student;

IN operator is a logical operator in SQL used to test whether specific value matches in a given list. It is an alternative of OR operator.

SELECT * FROM Products
WHERE price  IN ('200', '300', '400'); 

The BETWEEN operator is logical operator that is used to select records from a given range of values.

SELECT * FROM Products
WHERE price  BETWEEN 100 AND 200;  

LIKE operator is used to find records that matches with the specified pattern

 SELECT * FROM Students
 WHERE student_name LIKE 'm%';  

It is used to check whether the value is null or not.

SELECT address FROM Student WHERE address IS NULL; 

Similarly IS NOT NULL operator can be used.

 SELECT address FROM Student WHERE address IS NOT NULL; 

Primary key has two properties.

A tale can have only one primary key.

The LIMIT keyword is used to limit the number of rows returned by SQL query.

 SELECT * FROM Products LIMIT 20; 

This will return 20 records from the table. Now to select records from 21-40, OFFSET can be used in SQL query.

 SELECT * FROM Products LIMIT 20 OFFSET 21; 

This can also be shortened like

 SELECT * FROM Products LIMIT 20, 21

Using Limit to find second highest salary from employees table

SELECT * FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

Without using limit clause

SELECT MAX(salary) FROM employees WHERE salary NOT IN(SELECT MAX(salary) FROM employees);

using < operator

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); 

SQL Inner join returns the results that are common in both the tables.

SELECT *  FROM table1 INNER JOIN table2  ON table1.column_name = table2.column_name;  

SELECT *  FROM table1 JOIN table2  ON table1.column_name = table2.column_name;  

A foreign key is a column or set of column in a table whose value depends upon primary key of another table.

Example to create foreign key for Customer_orders tables that depends on Id (primary key) of customer table

CREATE TABLE Customer_orders(    
   ID  INT  NOT NULL,    
   DATE  DATETIME,     
   CUSTOMER_ID INT 
   references customers(ID),    
   AMOUNT     double,    
   PRIMARY KEY (ID) 
); 

just add database name before the column or table name.

SELECT db1.customer.name, 
db2.product.name   
FROM 
db1.customer INNER JOIN db2.product   
ON db1.customer.id = db2.product.id; 

When a table join with itself is called self join.

SELECT *
FROM table1 a, table1 b
WHERE condition; 

Composite key is set of more than one column that makes primary key. It can have at most one primary key.

OUTER JOIN : It returns the unmatched rows from both the table.

LEFT OUTER JOIN : It returns all the records from first table and matched records in table two.

SELECT columns FROM table1 
LEFT JOIN table2
ON table1.column_name = table2.column_name

SELECT * from employees 
LEFT JOIN offices
ON employees.office_id = offices.office_id

RIGHT OUTER JOIN : It returns all the records from second table and matched record in the first table

SELECT columns FROM table1 
RIGHT JOIN table2
ON table1.column_name = table2.column_name 

SELECT * from employees 
LEFT JOIN offices
ON employees.office_id = offices.office_id 

An EQUI JOIN is a join with a join condition containing an equality operator. It returns only the rows that have equivalent values for the specified columns

The USING clause is used to match only one column when more than one column matches. It is used to specify to specify the columns for the EQUI JOIN where several columns have the same names but not same data types. The NATURAL JOIN and USING clauses are mutually exclusive .

SELECT first_name, address FROM employees JOIN offices USING (office_id)

It is type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only . Here the matched column data type must be same. Do not use ON clause in Natural join.

SELECT * FROM employees NATURAL JOIN offices

The cross join returns result set in which the number of rows in the first table multiplied by the number of rows in second table. It is more like Cartesian product if no where clause is used. With where clause it is same like INNER JOIN

SELECT * FROM employees CROSS JOIN offices

 INSERT INTO table_name (column1, column2, column3, ...)
 VALUES (value1, value2, value3, ...); 

This function returns the AUTO_INCREMENT id of the last row that has been inserted or updated in a table.

 SELECT LAST_INSERT_ID(); 

 CREATE TABLE your_table_name (
      column_name1 datatype,
      column_name2 datatype,
    ....
 );  

CREATE TABLE employee(
  id  int,
  employee_name varchar(25),
  address varchar(255)
);

Using SELECT INTO statement

 SELECT * INTO table1 FROM table2 

 UPDATE table_name
 SET column1 = value1, column2 = value2, ...
 WHERE condition;  

UPDATE employee SET employee_name = "Jass" WHERE id = 1;

A subquery is a query within the query. A subquery must be enclosed within parenthesis.

subquery in SELECT Statement

 SELECT * 
    FROM sql_hr.employees 
    WHERE employee_id IN (SELECT employee_id
          FROM sql_hr.employees 
          WHERE SALARY > 4500) ;

DELETE FROM table_name WHERE column1 = value;

DELETE FROM employee WHERE id = 1;

GROUP BY clause is used to group results on the basis of one or more columns.

SELECT * 
    FROM sql_hr.employees 
    WHERE office_id  < 4
   GROUP BY salary

HAVING clause is used to find results that matches with specific value.

SELECT * 
    FROM sql_hr.employees 
    WHERE office_id  < 4
   GROUP BY salary
   HAVING employee_id < 37851

The ROLLUP command is used to include extra rows along with the total of rows.

SELECT * 
    FROM sql_hr.employees
   GROUP BY salary, office_id WITH ROLLUP

They both are used to combine data from different tables into single result. JOINS are usually faster than subquery. Subquery have more readabilityas compared to joins.

ALL keyword is used with WHERE and HAVING clause. IT returns true if all the sub query value matches the condition.

SELECT *
FROM employees
WHERE office_id = ALL (SELECT office_id FROM offices WHERE state = "VA"); 

They both are used with WHERE and HAVING clause.
ANY : returns true if any of sub query matches the value.
SOME : returns true if some of sub query matches the value.

SELECT *
FROM employees
WHERE office_id = ANY (SELECT office_id FROM offices WHERE state = "OH"); 

SELECT *
FROM employees
WHERE office_id = SOME (SELECT office_id FROM offices WHERE state = "OH");

It returns true if the sub query returns one or more results.

SELECT * FROM employees WHERE EXISTS (SELECT office_id FROM offices WHERE state = "OH"); 

SELECT first_name
FROM sql_hr.employees
WHERE office_id IN (SELECT office_id
FROM sql_hr.offices
WHERE state = "NY")

SELECT i.first_name,i.last_name
FROM ( SELECT office_id   FROM sql_hr.offices WHERE state = "OH") AS STATE , sql_hr.employees AS i
       WHERE i.office_id = STATE.office_id;

It is used to round values up-to a digit.

 SELECT ROUND(160.896, 3);  

Using GETDATE() function.

 SELECT  GETDATE();  

ISNULL() function take two arguments whereas there is no limit on number of arguments inn COALESC() function. In COALESC() function arguments must be of same data type.

SELECT IFNULL(NULL, "W3Schools.com");

this function checks the condition if it is true returns true otherwise false.

SELECT IF(1200<10, "TRUE", "FALSE"); 

This is used to to check for multiple cases

SELECT first_name, salary,
 CASE
     WHEN salary > 40000    THEN 'Salary Greater than 10000'
     WHEN salary < 40000 THEN 'Salary less than 10000'
     ELSE 'The quantity is under 30'
 END AS Salary
 FROM sql_hr.employees; 

Views are virtual tables based on the SQL select statement.

Advantages of Views : They always shows the updated data. Views hide the complexity of the data. They are used for security purpose as to the database users views are just real tables

To create a view

CREATE VIEW MYVIEW  AS
  SELECT    first_name, last_name
  FROM employees
  WHERE   office_id = 1;

To drop View

 DROP VIEW MYVIEW;  

CHECK Clause is used to check condition when the value is being entered into table.

CREATE TABLE employee_info(
    ID   INT              NOT NULL,
    NAME VARCHAR (20)     NOT NULL,
    AGE  INT              NOT NULL 
    ADD CONSTRAINT check_age CHECK (AGE >= 18),
    ADDRESS  CHAR (25) ,
    SALARY   DECIMAL (18, 2),       
    PRIMARY KEY (ID)
 );

Add CHECK constraint after table being created

 ALTER TABLE employee_info MODIFY AGE INT NOT NULL CHECK (AGE >= 18 ); 

To remove check constraint

 ALTER TABLE employee_info DROP CONSTRAINT  check_age;  

Stored procedures are set of SQL statements that can be stored, reused and shared by multiple programs.

To create a stored procedure

DELIMITER $$
 CREATE
 PROCEDURE emp_detail()
 BEGIN
 SELECT * FROM employees;
 SELECT * FROM offices;
 END $$
 DELIMITER ;

$$ or // can be used

call sql_hr.emp_detail();

EXEC procedure_name;

EXECUTE procedure_name;

DROP PROCEDURE sql_hr.emp_detail;

Both commands are used for deletion operation.

DROP command is used to drop a table or database. If table is droped, this will no longer be available in database.

DROP TABLE tablename;
DROP DATABASE databasename;

TRUNCATE command is used to delete a table but it keeps the table structure. It just empty the table means delete all its data.

TRUNCATE TABLE tablename;

TRUNCATE command is usually faster.

 DELIMITER $$
 CREATE FUNCTION emp_grade(
     salary DECIMAL(10,2)
 ) 
 RETURNS VARCHAR(20)
 DETERMINISTIC
 BEGIN
     DECLARE empGrade VARCHAR(20);
         IF salary > 50000 THEN    
           SET empGrade = 'GROUP 1'; 
         ELSEIF (salary >= 50000 AND  salary <= 100000) THEN    
           SET empGrade = 'GROUP 2'; 
         ELSEIF salary < 10000 THEN     
           SET empGrade = 'GROUP 3'; 
        END IF; 
RETURN (empGrade);
END$$
DELIMITER ; 

calling a function

SELECT 
     first_name, 
     emp_grade(salary)
 FROM
    sql_hr.employees
 ORDER BY 
     first_name

drop function

DROP FUNCTION CustomerLevel;

Trigger is a stored procedure that runs automatically when specific event is fired. For example when student marks is entered into the database their marks total is automatically calculated.

create trigger [trigger_name]  
[before | after]   {
 insert | update | delete
}   
on [table_name]   
[for each row]   
[trigger_body]  

DROP TRIGGER trigger_name;

Candidate key is set of one or more than one column having unique values. Every table have at least one candidate key. Candidate key is eligible for primary key. A table has more than one candidate key but primary key will be only one.

indexes are used to retrieve data from SQL in a speedy way.

CREATE UNIQUE INDEX index_name
on table_name (column_name);

 CREATE INDEX index_name on table_name (column1, column2); 

DROP INDEX index_name;

REVOKE is used to revoke permission from user on tables

REVOKE insert, update ON customer FROM abc@localhost;

GRANT is used to give permission to user on tables

GRANT insert, update ON customer TO abc@localhost;