1) What is SQL ?
SQL stands for Structured Query Language. SQL is an ANSI (American National Standards Institute) standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database.
2) What’s difference between DBMS and RDBMS ?
DBMS provides a systematic and organized way of storing, managing and retrieving from collection of logically related information. RDBMS also provides what DBMS provides but above that it provides relationship integrity. So in short we can say RDBMS = DBMS + REFERENTIAL INTEGRITY These relations are defined by using “Foreign Keys” in any RDBMS. Almost all DBMS (SQL SERVER, ORACLE etc) fulfills all the twelve CODD rules and are considered as truly RDBMS.
3) What are DML and DDL statements?
DML stands for Data Manipulation Statements. They update data values in table. Below are the most important DDL statements:-
=>SELECT – gets data from a database table
=> UPDATE – updates data in a table
=> DELETE – deletes data from a database table
=> INSERT INTO – inserts new data into a database table
DDL stands for Data definition Language. They change structure of the database objects like table, index etc. Most important DDL statements are as shown below:-
=>CREATE TABLE – creates a new table in the database.
=>ALTER TABLE – changes table structure in database.
=>DROP TABLE – deletes a table from database
=> CREATE INDEX – creates an index
=> DROP INDEX – deletes an index
4) How do we select distinct values from a table?
DISTINCT keyword is used to return only distinct values. Below is syntax:-
SELECT DISTINCT age FROM Employee
5) What is Like operator for and what are wild cards?
LIKE operator is used to match patterns. A “%” sign is used to define the pattern. Below SQL statement will return all words which start with letter “S”
SELECT * FROM Employee WHERE EmpName LIKE 'S%'
Below SQL statement will return all words which end with letter “S”
SELECT * FROM Employee WHERE EmpName LIKE '%S'
Below SQL statement will return all words having letter “S” in between
SELECT * FROM Employee WHERE EmpName LIKE '%S%'
6) Can you explain Insert, Update and Delete query?
Insert statement is used to insert new rows in to table. Update to update existing data in the table. Delete statement to delete a record from the table. Below code snippet for Insert, Update and Delete :-
INSERT INTO Employee SET name='rohit',age='24'; UPDATE Employee SET age='25' where name='rohit'; DELETE FROM Employee WHERE name = 'sonia';
7) What is order by clause?
ORDER BY clause helps to sort the data in either ascending order to descending order. Ascending order sort query
SELECT name,age FROM Employee ORDER BY age ASC
Descending order sort query
SELECT name FROM Employee ORDER BY age DESC
8) What is the SQL ” IN ” clause?
SQL IN operator is used to see if the value exists in a group of values. For instance the below SQL checks if the Name is either ‘rohit’ or ‘Anuradha’
SELECT * FROM Employee WHERE name IN ('Rohit','Anuradha')
Also you can specify a not clause with the same.
SELECT * FROM Employee WHERE age NOT IN (17,16)
9) Can you explain the between clause?
Below SQL selects employees born between ’01/01/1975′ AND ’01/01/1978′
SELECT * FROM Employee WHERE DOB BETWEEN '1975-01-01' AND '2011-09-28'
10) What are different types of joins in SQL?
Inner join shows matches only when they exist in both tables.
SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID
LEFT OUTER JOIN
Left join will display all records in left table of the SQL statement.
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID
RIGHT OUTER JOIN
Right join will display all records in right table of the SQL statement.
SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID
11) What is “CROSS JOIN”? or What is Cartesian product?
“CROSS JOIN” or “CARTESIAN PRODUCT” combines all rows from both tables. Number of rows will be product of the number of rows in each table.
12) How to select the first record in a given set of rows?
Select top 1 * from sales.salesperson
13) Can you explain the SELECT INTO Statement?
SELECT INTO statement is used mostly to create backups. The below SQL backs up the Employee table in to the EmployeeBackUp table. One point to be noted is that the structure of EmployeeBackup and Employee table should be same.
SELECT * INTO EmployeeBackup FROM Employee
14) What is a “trigger”?
Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurrence of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements.
15) What type of wildcards have you used?
Wildcards are special characters that allow matching string without having exact match. In simple word they work like contains or begins with. Wildcard characters are software specific and in SQL Server we have % which represent any groups of characters, _ that represent one character (any) and you also get  where we can [ab] which means characters with letter a or b in a specific place.
16) What is the difference between clustered and non clustered index in SQL?
The differences between clustered and non clustered index in SQL are:
– Clustered index is used for easy retrieval of data from the database and its faster whereas reading from non clustered is relatively slower.
– Clustered index alters the way records are stored in a database as it sorts out rows by the column which is set to be clustered index whereas in a non clustered index, it does not alter the way it was stored but it creates a separate object within a table which points back to the original table rows after searching.
– One table can only have one clustered index whereas it can have many non clustered index.
SELECT * FROM Table_Name Where EmpName like 'A%'
18) Write a SQL query to get the third highest salary of an employee from employee_table?
SELECT TOP 1 salary FROM ( SELECT TOP 3 salary from employee_table ORDER BY salary DESC) AS emp ORDER BY salary ASC;
19) What is the difference between DELETE and TRUNCATE commands?
- Delete is a DML command but Truncate is a DDL command
- We can use Where clause in Delete command but not in Truncate command
- Delete statement is used to delete a row from a table but Truncate command is used to remove all the rows from a table
- Delete is slower than Truncate statement. Truncate is faster than Delete statement
- You can rollback data after using DELETE statement. It is not possible to roll back after using TRUNCATE statement
SELECT COUNT(*) FROM Employees WHERE DOB BETWEEN '01/01/1990' AND '31/12/1999'
21) Write an SQL query to find the year from Date.
SELECT YEAR(GETDATE()) as "Year";
22) Write SQL Query to delete duplicate rows in a database
DELETE from emp a WHERE rowid != (SELECT MAX(rowid) FROM emp b WHERE a.empno=b.empno);
23) What is view in SQL?
A view can be defined as a virtual table that contains rows and columns with fields from one or more table. Code syntax:
CREATE VIEW view_name AS SELECT column_name FROM table_name WHERE condition
24) What is a cursor?
A cursor is a database object which is used to manipulate data in a row-to-row manner.
Cursor follow steps as given below:
- Declare cursor
- Open cursor
- Retrieve row from cursor
- Process the row
- Close cursor
- Deallocate cursor
INSERT INTO table2 (column1, column2, column3, ...) SELECT column1, column2, column3, ... FROM table1 WHERE condition;
26) What is Auto Increment feature in SQL?
Auto increment allows the user to create a unique number to be generated whenever a new record is inserted in the table. IDENTITY keyword can be used in SQL SERVER for auto incrementing.
27) Which query operators in SQL is used for pattern matching?
LIKE operator is used for pattern matching, and it can be used as :-
% – Matches zero or more characters
_ – Matching exactly one character
28) What are the constraints available in SQL?
Constraints in SQL are – Primary Key, Foreign Key, Unique Key, Not Null, Check and Index
29) What is the difference between Primary and Unique key?
- There should be only one Primary Key in a table whereas there can be any number of Unique keys
- Primary Key doesn’t allow null values whereas Unique key allows Null values
- AVG() – This function returns the average value
- COUNT() – This function returns the number of rows
- MAX() – This function returns the largest value
- MIN() – This function returns the smallest value
- ROUND() – This function rounds a numerical field to the number of decimals specified
- SUM() – This function returns the sum
31) How to add a column ‘Salary’ to a table Employee?
ALTER TABLE Employee ADD (Salary)
32) How to get list of all tables from a database?
USE TestDB GO SELECT * from sys.Tables GO
33) How to fetch values from Table1 that are not in Table2 without using NOT keyword?
By using the except keyword
SELECT * from Table1 Except Select * from Table2
34) How to rename a column in the output of SQL query?
SELECT column_name AS new_column_name FROM table_name
35) How to rename a table?
SP_RENAME TABLE 'Test1','Test2'