Sql interview Questions

23. What is the difference between Rename and Alias?

'Rename' is a permanent name given to a table or column.'Alias' is a temporary name given to a table or column.

What is a CHECK constraint?

A CHECK constraint is used to limit the value that is accepted by one or more columns. E.g. 'Age' field should contain only the value greater than 18. CREATE TABLE EMP_DETAILS(EmpID int NOT NULL, NAME VARCHAR (30) NOT NULL, Age INT CHECK (AGE > 18), PRIMARY KEY (EmpID));

What is a FOREIGN KEY?

A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY in a table is linked with the PRIMARY KEY of another table.

What is Stored procedure?

A Stored Procedure is a collection of SQL statements that have been created and stored in the database to perform a particular task. A program written in the database , when required you invoke the store procedure.

20. What is a Subquery ?

A Subquery is a SQL query within another query. It is a subset of a Select statement whose return values are used in filtering the conditions of the main query. SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ;

What is a Trigger?

A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee_Payroll, Employee_Time_Sheet etc.,

What is a column in a Table?

A column is a vertical entity in a table that contains all information associated with a specific field in a table.

1. What is a Database?

A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views and other database objects.

5. What is a Field in a Database?

A field in a Database table is a space allocated to store a particular record within a table.

What is a NULL value?

A field with a NULL value is a field with no value. A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation. Assume, there is a field in a table is optional and it is possible to insert a record without adding a value to the optional field then the field will be saved with a NULL value.

What is a Record in a Database?

A record (also called a row of data) is an ordered set of related data in a table.

Can a table contain multiple FOREIGN KEY's?

A table can have many FOREIGN KEY's.

4. What is a Table in a Database?

A table is a database object used to store data in a field in the form of columns and rows that holds the records.

29. What is a Unique constraint?

A unique constraint is used to ensure that there are no duplication values in the field/column.

59. How to add a column 'Salary' to a table Employee_Details?

ALTER TABLE Employee_Details ADD (Salary);

17. What is an Index?

An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns. Create index_IX tblEmpl_salary on tblEmpl(Salary ASC)

What is the difference between an inner and outer join?

An inner join returns rows when there is at least some matching data between two (or more) tables that are being compared(only shows intersecting data between two table) An outer join returns rows from both tables that include the records that are matched and unmatched( put it as null value) from one or both the tables.

What are Operators available in SQL?

Arithmetic Operators(+, -, /, *, %) Comparison Operators( >, =, <, <>, <=, >=) Logical Operators(All, And, Any, Between, IN, Like, not, or , some) Compound Operators(+=, -=, ..) Bitwise Operators(Bitwise AND, Bitwise OR, Bitwise Exclusive OR)

What is the difference between NULL value, Zero, and Blank space?

As I mentioned earlier, Null value is field with no value which is different from zero value and blank space.Null value is a field with no value.Zero is a number.Blank space is the value we provide. The ASCII value of space is CHAR(32).

What is Auto increment?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted. ID int NOT NULL AUTO_INCREMENT,

What is the difference between Union and Union All command?

Both Union and Union All concatenate the result of two tables Union: It omits duplicate records and returns only distinct result set of two or more select statements. Union All: It returns all the rows including duplicates in the result set of different select statements.

Which TCP/IP port does SQL Server run?

By default it is 1433

How to rename a column in the output of SQL query?

By using AS keyword SELECT column_name AS new_name FROM table_name;

How to get unique records from a table?

By using DISTINCT keyword. SELECT DISTINCT Col1, Col2 from Table1

How to get each name only once from an employee table?

By using the DISTINCT keyword, we could get each name only once. SELECT DISTINCT employee_name FROM employee_table;

How to fetch values from TestTable1 that are not in TestTable2 without using NOT keyword?

By using the EXCEPT keyword SELECT * FROM TestTable1 EXCEPT SELECT * FROM TestTable2;

What is CRUD operations?

CRUD represents an acronym for the database operations Create, Read, Update, and Delete. The communication between two layers could be in the form of ad hoc SQL statements such as INSERT, SELECT, UPDATE, and DELETE.

What is a Composite PRIMARY KEY?

Composite key, or composite primary key, refers to cases where more than one column is used to specify the primary key of a table. In such cases, all foreign keys will also need to include all the columns in the composite key. Note that the columns that make up a composite key can be of different data types. Oracle CREATE TABLE SAMPLE_TABLE (COL1 integer, COL2 varchar(30), COL3 varchar(50), PRIMARY KEY (COL1, COL2));

DCL commands

DCL commands are used to create roles, grant permission and control access to the database objects.GRANT: To provide user access DENY: To deny permissions to users REVOKE: To remove user access

13. What are the different DDL commands in SQL?

DDL commands are used to define or alter the structure of the database.CREATE: To create databases and database objects. ALTER: To alter existing database objects.DROP: To drop databases and databases objects.TRUNCATE: To remove all records from a table but not its database structure.RENAME: To rename database objects

What is a DEFAULT constraint?

DEFAULT constraint is used to include a default value in a column when no value is supplied at the time of inserting a record.

Write the command to remove all Players named Sachin from the Players table.

DELETE from Players WHERE Player_Name = 'Sachin'

14. What are the different DML commands in SQL?

DML commands are used for managing data present in the database.SELECT: To select specific data from a database.INSERT: To insert new records into a table.UPDATE: To update existing records.DELETE: To delete existing records from a table

8. What is DBMS?

Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.

List out the ACID properties and explain?

Following are the four properties of ACID. These guarantees that the database transactions are processed reliably. Atomicity, Consistency, Isolation, and Durability

3. What is the difference between GUI Testing and Database Testing?

GUI Testing is AKA User Interface Testing or Front-end testing.Database Testing is AKA back-end testing or data testing.GUI Testing deals with all the testable items that are open to the user to interaction such as Menus, Forms etc.Database Testing deals with all the testable items that are generally hidden from the user.The tester who is performing GUI Testing doesn't need to know Structured Query Language.GUI Testing includes invalidating the text boxes, check boxes, buttons, drop-downs, forms etc., majorly the look and feel of the overall application.Database Testing involves in verifying the integrity of data in the front end with the data present in the back end. It validates the schema, database tables, columns, indexes, stored procedures, triggers, data duplication, orphan records, junk records. It involves in updating records in a database and verifying the same on the front end.

How to add new Employee details in an Employee_Details table with the following details Employee_Name: John, Salary: 5500, Age: 29?

INSERT into Employee_Details (Employee_Name, Salary, Age) VALUES ('John', 5500 , 29);

45. Explain SQL Data Types?

In SQL Server, each column in a database table has a name and a data type. We need to decide what type of data to store inside each and every column of a table while creating a SQL table.eg. char, varchar, numbers,etc..

25. What are the different types of joins?

Inner join,Outer Join,Right Join,Left join,

What is a database Schema

It is a diagramatically, visually and graphically represent a database.Particularly a relational database. It is an ERD.

What is Database Testing?

It is back-end testing or data testing.Database testing involves in verifying the integrity of data in the front end with the data present in the back end. It validates the schema, database tables, columns, indexes, stored procedures, triggers, data duplication, orphan records, junk records. It involves in updating records in a database and verifying the same on the front end.

Which operator is used in query for pattern matching?

It is called wild card character.LIKE operator is used for pattern matching, and it can be used as -. % - Matches zero or more characters. _(Underscore) - Matching exactly one character. MySQL Select * from Student where studentname like 'a%' 1 Select * from Student where studentname like 'amk_'

what is sql join ?

Join helps retrieving data from two or more table when there is a common column in between them. In the other hand they are mutually related using primary key and foreign key. SELECT table1 , SELECT table2 SELECT * from Emplyoee INNER JOIN Salary on Emplyoee.name=Salary.name;

What is a Join?

Join is a query, which retrieves RELATED columns or rows from multiple tables.

Can a table contain multiple PRIMARY KEY's?

No There can only be one primary key in each table.A primary key is a field in a table which uniquely identifies each row/record in a database table. All columns participating in a primary key constraint must not contain NULL values.

What is Normalization?

Normalization is the process of efficiently organizing data in a database by eliminating redundant data and ensuring the data dependency. (only storing related data in a table)

What are the popular Database Management Systems in the IT Industry?

Oracle, MySQL, Microsoft SQL Server, PostgreSQL, Sybase, MongoDB, DB2, and Microsoft Access etc.,

What is the order of SQL SELECT?

Order of SQL SELECT statement is as follows SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY.

9. What is RDBMS?

RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed using Structured Query Language (SQL)

Write an SQL Query to find list of Employee_Name start with 'E' from the below table

SELECT * FROM Employee_Details WHERE Employee_Name like 'E%';

Write an SQL Query to find an Employee_Name whose Salary is equal or greater than 5000 from the below table Employee_Details.

SELECT Employee_Name FROM Employee_Details WHERE Salary>=5000;

Write SQL SELECT query that returns the FirstName and LastName from Employee_Details table.

SELECT FirstName, LastName FROM Employee_Details;

How to display the current date in SQL.

SELECT GetDate();

How to Test for NULL Values?

SELECT column_names FROM table_name WHERE column_name IS NULL;

How to rename a Table?

SP_RENAME TABLE 'SCOREBOARD', 'OVERALLSCORE'

What is the SQL CASE statement?

SQL Case statement allows embedding an if-else like clause in the SELECT statement

11. What is SQL?

SQL Overview: SQL stands for Structured Query Language. It is an American National Standard Institute (ANSI) standard. It is a standard language for accessing and manipulating databases. Using SQL, some of the action we could do are to create databases, tables, stored procedures (SP's), execute queries, retrieve, insert, update, delete data against a database.

56. What are aggregate functions in SQL?

SQL aggregate functions return a single value, calculated from multiple values in a column. Some of the aggregate functions in SQL are as follows 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 numeric field to the number of decimals specified SUM() - This function returns the sum

12. What are the different types of SQL commands?

SQL commands are segregated into following types: DDL - Data Definition Language DML- Data Manipulation language DCL- Data Control Language

27. What are SQL constraints?

SQL constraints are the set of rules that enforced some restriction while inserting, deleting or updating of data in the databases.

57. What are string functions in SQL?

SQL string functions are used primarily for string manipulation. Some of the widely used SQL string functions are LEN() - It returns the length of the value in a text field LOWER() - It converts character data to lower case UPPER() - It converts character data to upper case SUBSTRING() - It extracts characters from a text field LTRIM() - It is to remove all whitespace from the beginning of the string RTRIM() - It is to remove all whitespace at the end of the string CONCAT() - Concatenate function combines multiple character strings together REPLACE() - To update the content of a string.

How to select all the odd number records from a table?

Select * from table where id % 2 != 0

How to select all the even number records from a table?

Select * from table where id % 2 = 0

Write an SQL Query to select all records from the table?

Select * from table_name;

Describe SQL comments?

Single Line Comments: Single line comments start with two consecutive hyphens (-) and ended by the end of the line Multi-Line Comments: Multi-line comments start with /* and end with */. Any text between /* and */ will be ignored.

28. What are the constraints available in SQL?

Some of the constraints in SQL are - Primary Key, Foreign Key, Unique Key, SQL Not Null, Default, Check and Index constraint.

46. What are the possible values that can be stored in a BOOLEANdata field?

TRUE and FALSE

What is a Primary Key?

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only one primary key, which may consist of single or multiple fields.

51. Define the SELECT INTO statement.

The SELECT INTO statement copies data from one table into a new table. The new table will be created with the column-names and types as defined in the old table. You can create new column names using the AS clause SELECT * INTO newtable FROM oldtable WHERE condition;

How to avoid duplicate records in a query?

The SQL SELECT DISTINCT query is used to return only unique values. It eliminates all the duplicate values. SQL> SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

GROUP BY clause

The clause that groups rows based on the specified column. group by returns one records for each group. count, min , max, sum , avg SELECT department _ID sum(salary)from employes group by department_ID.

What is the largest value that can be stored in a BYTE data field?

The largest number that can be represented in a single byte is 11111111 or 255. The number of possible values is 256 (i.e. 255 (the largest possible value) plus 1 (zero), or 28).

What is the difference between UNIQUE and PRIMARY KEY constraints?

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.

How to change a value of the field 'Salary' as 7500 for an Employee_Name 'John' in a table Employee_Details?

UPDATE Employee_Details set Salary = 7500 where Employee_Name = 'John';

What is the difference between Having and Where clause?

Where clause is used to fetch data from a database that specifies particular criteria whereas a Having clause is used along with 'GROUP BY' to fetch data that meets particular criteria specified by the Aggregate functions. Where clause cannot be used with Aggregate functions, but the Having clause can. eg: SELECT column name FROM table name WHERE condition GROUP BY columns names HAVING condition ORDER BY column names SELECT Department_id, SUM (salary)from employees WHERE department_id HAVING sum (salary)>20000 ORDER BY sum(salary);

To rename Table Name & Column Name

sp_rename 'TableName.OldColumnName', 'NewColumnName'

HAVING clause

to filter the results grouped by a GROUP BY clause. Can only reference aggregate functions or column that are also specified in the GROUP BY clause. Where-----Group By-----Having SELECT dept_id, SUM(Salary)from emp WHERE dept_id <>50 GROUP BY dept_Id having sum (salary)>10000.

Wild card and light keyword in database?

way of defining a pattern that we want to match a specific pattern. eg; find any client who are in llc. SELECT * from client where client_name LIKE '% llc'; % is any number of character._ represents on character.