SQL Interview Questions

 

                                 

Q1. What is a Database ?

A database is an organized collection of data, stored and retrieved digitally from a remote or local computer system. Databases can be vast and complex, and such databases are developed using fixed design and modern approaches.

Q2. What is DBMS ?

DBMS Stands for Database management System. DBMS is a system software that is responsible for the creation, retrieval and updation and management of the database. It ensures that our data is consistent, organized and is easily accessible by serving as an interface between the database and end-users or application software.

Q3. What is RDBMS ? How it is different from DBMS ?

RDBMS Stands for Relational Database Management System. The key difference here, compared to DBMS, is that RDBMS stored data in the form of a collection of tables, and relation can be defined between the common fields of these tables. Most modern database management systems like MYSQL, Microsoft SQL Server, Oracle, IBM DB2, and Amazon Redshift are based on RDBMS. 

Q4. What is SQL ?

SQL Stands for Structured Query Language, and it is used to access the Database. This is a standard language used to perform tasks such as retrieval, updation, insertion and deletion of data from a Database. 

Q5. What is the difference between SQL and MYSQL ?

SQL is a standard language for retrieving and manipulating structured databases. On the contrary, MYSQL is a relational database management system, like SQL Server, Oracle or IBM DB2, that is used to manage SQL databases.

Q6. What are tables and fields ?

A table is an organized collection of data stored in the form of rows and columns. Columns can be categorized as vertical and rows as horizontal. The columns in a table are called tables while the rows can be referred to as records. 

Example: 

Table: Employee

Field: Emp ID, Emp Name, Date of Birth.

Data: 201576, Karthik, 11/12/1989

Q7. What are Constraints in SQL ?

Constraints are used to specify the rules concerning data in the table. It can be applied for single or multiple fields in a SQL table during the creation of the table or after using the ALTER TABLE Command. The Constraints are :

• NOT NULL - Restricts NULL values from being inserted into a column. 
• CHECK - Verifies that all values in a field satisfy a condition.
• DEFAULT - Automatically assigns a default value if no value has been specified for the field.
• UNIQUE - Ensures unique values to be inserted into the field.
• INDEX - Indexes a field providing faster retrieval of records.
• PRIMARY KEY - Uniquely identifies each record in a table.
• FOREIGN KEY - Ensures referential integrity for a record in another table.

Q8. What is a Primary Key ?

The PRIMARY KEY constraints uniquely identifies each row in a table. It must contain UNIQUE values and has an implicit NOT NULL constraint.

A table in SQL is strictly restricted to have one and only primary key, which is comprised of single or multiple fields(Columns).

CREATE TABLE Students ( /* Create table with a single field as primary key */ ID INT NOT NULL Name VARCHAR(255) PRIMARY KEY (ID) ); CREATE TABLE Students ( /* Create table with multiple fields as primary key */ ID INT NOT NULL LastName VARCHAR(255) FirstName VARCHAR(255) NOT NULL, CONSTRAINT PK_Student PRIMARY KEY (ID, FirstName) ); ALTER TABLE Students /* Set a column as primary key */ ADD PRIMARY KEY (ID); ALTER TABLE Students /* Set multiple columns as primary key */ ADD CONSTRAINT PK_Student /*Naming a Primary Key*/ PRIMARY KEY (ID, FirstName);

Q9. What is a UNIQUE constraint ?

A UNIQUE constraint ensures that all values in a column are different. This provides uniqueness for the columns and helps to identify each row uniquely. Unlike primary key, there can be used interchangeably. 

CREATE TABLE Students ( /* Create table with a single field as unique */ ID INT NOT NULL UNIQUE Name VARCHAR(255) ); CREATE TABLE Students ( /* Create table with multiple fields as unique */ ID INT NOT NULL LastName VARCHAR(255) FirstName VARCHAR(255) NOT NULL CONSTRAINT PK_Student UNIQUE (ID, FirstName) ); ALTER TABLE Students /* Set a column as unique */ ADD UNIQUE (ID); ALTER TABLE Students /* Set multiple columns as unique */ ADD CONSTRAINT PK_Student /* Naming a unique constraint */ UNIQUE (ID, FirstName);

Q10. What is a Foreign Key ?

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table. Foreign Key constraint ensures referential integrity in the relation between the two tables. 

The table with the foreign key constraint is labeled as a child table, and the table containing the candidate key is labeled as the referenced or parent table.

CREATE TABLE Students ( /* Create table with foreign key - Way 1 */ ID INT NOT NULL Name VARCHAR(255) LibraryID INT PRIMARY KEY (ID) FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) ); CREATE TABLE Students ( /* Create table with foreign key - Way 2 */ ID INT NOT NULL PRIMARY KEY Name VARCHAR(255) LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID) ); ALTER TABLE Students /* Add a new foreign key */ ADD FOREIGN KEY (LibraryID) REFERENCES Library (LibraryID);

Q11. What are the different types of statements ?

 (a) DDL (Data Definition Language) : it is used to define the database structure such as tables. It includes three statements such as CREATE, ALTER, and  DROP.

Some of the DDL Commands are listed below :

CREATE: It is used to create the table

CREATE TABLE table_name

column_name1 data_type(size),

column_name2 data_type(size),

column_name3 data_type(size),

ALTER: The ALTER table is used for modifying the existing table object in the database.

ALTER TABLE table_name

 ADD column_name datatype




OR




ALTER TABLE table_name

DROP COLUMN column_name

(b) DML (Data Manipulation Language): These statements are used to manipulate the data in records.

Commonly used DML statements are INSERT, UPDATE and DELETE.

The SELECT statement is used as a partial DML statement, used to select all or relevant records in the table.

(c) DCL (Data Control Language): These statements are used to set privileges such as GRANT and REVOKE

database access permission to the specific user.

Q12. What are the different clauses used in SQL ?

WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill

the given condition

Syntax:

SELECT column_name(s)  FROM table_name  WHERE condition; GROUP Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the end result accordingly. Syntax: SELECT column_name(s)  FROM table_name  GROUP BY column_name; HAVING Clause: This Clause is used in association with the GROUP BY Clause. It is applied to each group of results or the entire result as a single group. It is much similar as WHERE Clause but the only difference is you cannot use it without GROUP BY Clause. Syntax: SELECT column_name(s)  FROM table_name  GROUP BY column_name  HAVING condition; ORDER BY Clause: This Clause is used to define the order of the query output either in ascending (ASC) or in descending (DESC). Ascending (ASC) is set as default one but descending (DESC) is set explicity. Syntax: SELECT column_name(s)  FROM table_name  WHERE condition  ORDER BY column_name ASC|DESC; USING Clause: USING Clause comes in use while working with SQL JOIN. It is used to check quality based on columns when tables are joined. It can be used instead of the ON clause in JOIN. Syntax: SELECT column_name(s)  FROM table_name  JOIN table_name  USING (column_name); Q13 What are the different JOINS used in SQL ? 4 Major types are used while working on multiple joins in SQL databases: INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from both tables when it has at least one matching column Syntax: SELECT column_name(s)  FROM table_name1   INNER JOIN table_name2  ON column_name1=column_name2; LEFT JOIN(LEFT OUTER JOIN): This JOIN returns all rows from the LEFT table and its matched rows from a RIGHT table. Syntax: SELECT column_name(s)  FROM table_name1  LEFT JOIN table_name2  ON column_name1=column_name2; RIGHT JOIN(RIGHT OUTER JOIN): This JOIN returns all rows from the RIGHT table and its matched rows from a LEFT table Syntax: SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON column_name1=column_name2; FULL JOIN(FULL OUTER JOIN): This JOIN returns all results when there is a match either in a RIGHT table or in the LEFT table Syntax: SELECT column_name(s)  FROM table_name1  FULL OUTER JOIN table_name2  ON column_name1=column_name2; Q14. How many Aggregate functions are avaliable in SQL ? SQL aggregate functions and calculate values from multiple columns in a table and returns a single value. There are 7 aggregate functions in SQL: AVG(): Returns the average values from specified columns. COUNT(): Returns number of table rows. MAX(): Returns the largest values among the records. MIN(): Returns the smallest values among the records. SUM(): Returns the sum of specified column values. FIRST(): Returns the first value. LAST(): Returns the last value. Q15. What are Scalar functions in SQL ? Scalar functions are used to return a single value based on the input values. Scalar functions are as follows: UCASE(): Converts the specified field in the upper case. LCASE(): Converts the specified field in lower case. MID(): Extracts and return the character from the text field. FORMAT(): Specifies the display format. LEN(): Specifies the length of the text field. ROUND(): Rounds up the decimal field value to a number. Q16. What are triggers ? Triggers in SQL is kind of stored procedure used to create a response to a specific action performed on the table such as INSERT, UPDATE or DELETE. You can invoke triggers explicitly on the table in the database. Action and event are two components of SQL triggers. When certain actions are performed, the event occurs in response to that action. Syntax: CREATE TRIGGER name {BEFORE|AFTER} (event [OR..]} ON table_name [FOR [EACH] {ROW|STATEMENT}] EXECUTE PROCEDURE functionname {arguments} Q17. Explain the working of SQL Privileges ? SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or form users of database objects by using commands like SELECT, INSERT, UPDATE, DELETE, ALL etc. GRANT Command: This command is used to provide database access to users other than the administrator. Syntax: GRANT privilege_name  ON object_name  TO {user_name|PUBLIC|role_name}  [WITH GRANT OPTION]; In the above syntax, the GRANT option indicates that the user can grant access to another user too. REVOKE Command: This command is used to provide database deny or remove access to database objects. Syntax: REVOKE privilege_name  ON object_name  FROM {user_name|PUBLIC|role_name}; Q18. What is the difference between SQL and PL/SQL ? SQL is a structured query language to create and access database whereas PL/SQL comes with procedural concepts of programming languages. Q19. What is the difference between DELETE and TRUNCATE ? The differences are : The basic difference in both is DELETE command is DML command and the TRUNCATE command is DDL. DELETE command is used to delete a specific row from the table whereas the TRUNCATE command is used to remove all rows from the table. We can use the DELETE command with WHERE clause but cannot use the TRUNCATE command with it. Q20. What is the differences between DROP and TRUNCATE ? TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it is also cannot be retrieved back.