recent

Titulo

SQL Interview Questions & Answers

The SQL Developer Interview Questions & Answers is for anyone who is applying for Application Developer, BI Developer, DBA, Business Analyst or anyone who will work with Relational Database Management Systems. This is a comprehensive list of all possible SQL questions that might be asked during an Interview. I strongly recommend reading “50 database terminologies that every Developer should know” before you begin this. The answers presented here are database independent unless otherwise mentioned. You can also do a search within our site for any questions that you need a detail information on. 

SQL General:

Q: What is SQL? SQL stands for Structure Query Language. It is a standard language to communicate with most Relational Database Management System which lets you access and manipulate data.

Q: What are the functions of SQL? SQL can execute queries against database. It retrieves, Inserts, Updates, Delete records from a table. SQL also allows you to Create, Drop and Alter database objects like Tables, Procedures, Views and Synonyms. Oracle, SQL Server, My SQL are some of the most popular DBMS available today.

Q: What are the functions of DBMS? DBMS stands for Database Management Systems. It is a software for creating and managing database. The function of a DBMS are Data Storage Management, Indexing, Views, Security, Integrity, Concurrency, Backup & Recovery, Database Design, documentation, and Data Manipulation.

Q: What are the subsets of SQL:  Data Definition Language (DDL),  Data Manipulation Language (DML) and Data Control Language (DCL) are three subsets of SQL.

Q: What is the difference between a file and a database? Database Systems provide a very organized way of storing and retrieving data from a logically organized data from a table. Structured Query language can access and update data. A file is a collection of file stored in an orderly manner in a file

Q: What is the difference between a DBMS and RDBMS? The key difference between them is that Relational Database Management System (RDBMS) stored data in a tabular form while Database Management Systems stores data as files.

Q: What is Normalization? Normalization is the process of organizing data to minimize redundancy. It usually involve diving a database into two or more tables and defining the relationships between the tables. There are three main normal forms 1NF, 2NF, 3NF. There are few additional normalization level like BCNF and 4NF.

Q: What is Denormalization? Denormalization is  an exact opposite of Normalization. It is an approach to combine two or more table into one to optimize the performance of database and a query. It is  very ideal for OLAP database systems like data-warehouse.

Q: What is E-R Diagram? Entity-Relationship Diagram (ERD) is a data modelling technique that graphically illustrates an information system's entitles and the relationship between those entities. It is crucial to while designing a good database.

Data Manipulation Language (DML):

Q: Explain DML? DML stands for Data Manipulation Language. It is used for Selecting, Inserting, Deleting and Updating of a data in a table. See below for all the available DML commands.
  1. SELECT: The Select statement returns a result from set from one or more table.
  2. INSERT: The Insert statement lets you Insert new records to a table.
  3. DELETE: The delete statement lets you delete existing record from a table.
  4. UPDATE: The update statement lets you Modify the existing record from a table.
  5. MERGE: Merge lets you select rows from one or more sources for Update or Insertion into a table or a view. You can specify the conditions to determine whether to Update or Insert into the target table or view.
Q: How is Delete different from Drop? Delete is a DML statement which delete records from a table. Based on your delete statement, it may delete a selected data or all data from a table. A Drop is a DDL statement which deletes all the records from a table along with the table structure. Delete statement logs each transaction to a log therefore it can be rollback if needed whereas Drop cannot be rollback because there is no redo log for it. Since no logs are written with Drop statement, it is much faster than Delete statement.

Data Definition Language (DDL):

Q: Explain DDL? Data Definition Language statement defines structurally change and drop of schema objects. For example, DDL statements enable you to Create, Alter and Drop schema's objects and other database structure including the database and users. Most DDL statements starts with the keywords ALTER, CREATE,  DROP and TRUNCATE. With DDL an implicit commits occurs immediately before the database executes a DDL statement and a COMMIT or ROLLBACK occurs immediately afterwards. DDL Examples:
  1. ALTER: Alter will let you modify existing database objects from your database. 
  2. CREATE: Create lets you create database objects like Table, View, Synonym, Index, etc in a database.
  3. DROP: Drop commands drop the database objects like Table, View, Synonym, Index, etc from a database. 
  4. TRUNCATE: Deletes all the data from a schema objects without removing the structure of these objects. Truncate generates no undo logs, therefore it is faster than a delete but the transaction can not be rolled back. Also, TRUNCATE does not invoke delete triggers.
Q: How is Drop different from Truncate? Drop removes the data along with the structure of a table or objects while Truncate deletes only data but keeps the structure of the table.

 Q: What are the difference between Delete and Truncate command? Truncate deletes all the records at one time while delete command deletes one record at a time. See below for more differences!

 Delete:
  1. Removes on record at a time with an entry in the transaction log for each deleted row
  2. Delete retain the identity counter
  3. Delete activates Triggers
  4. Delete can be rolled back
  5. Delete is a DML command
  6. Delete can be used with or without WHERE clause
  7. Much slower if deleting millions of records
Truncate:
  1. Truncate deletes all the records from a table with fewer system and transaction log resources than Delete statement.
  2. Truncate does not retain the Identity counter. It resets the identity column
  3. Truncate does not activate the trigger
  4. Truncate cannot be rolled back
  5. Truncate is a DDL command
  6. Truncate cannot be used with Where clause. It deletes all.
  7. Much faster then Delete.

Data Control Language (DCL):

Q: Explain DCL? DCL stands for Data Control Language which allows you to control the access to the database. DCL are important to manage database users and database security. GRANT and REVOKE are two popular DCL statements.
  1. GRANT: Lets specific user to perform specific task. The task  may include Select, Insert, Update, Delete, Drop, Alter, Create etc.
  2. REVOKE: To take the access or privileges away from a user on specific database, schema, or table.

SQL Operator: 

Q: What is an Operator in SQL? An operator is a reserved word or a character used primarily in a SQL WHERE clause to perform operations, such as comparisons and arithmetic operations. There are three types of operators.
  1. Arithmetic Operators
  2. Comparison Operators
  3. Logical Operators
Example of Operators:
  1. Arithmetic Operators: Addition (+), Subtraction (-), Multiplications(*), Division(/) and Modulus(%)
  2. Comparison Operators:  Equal (=), Not Equal(!=),  Not Equal (<>), Greater Than (>), Less Than(<), Greater Than or Equal to ( >=), Less Than or Equal to (=<),  Not Less than(!<), Not Greater Than (!>)
  3. Logical Operators: ALL, AND, ANY,BETWEEN, EXISTS, IN, LIKE,  NOT,NOT, OR, IS NULL, UNIQUE

View

Q:Explain View? A view is a virtual table based on the result-set of an SQL Statement.  It has rows and columns like in a real table but does not hold the data. A view with one one base table is a simple view whereas a view with multiple base tables is called a complex view.  Views can be read only or update-able view. Only Materialized or Indexed view will store data on a underlying table.

Q:What are advantages of using View?
  1. Views  represent the subset of data therefore it can limit the degree of exposure of the base tables.
  2. Simply the complex queries into a single virtual table
  3. It hides the complexity of data.
  4. Takes a very little space as it does not hold data
  5. Provides extra security.
Q:What are the disadvantage of using View?
  1. No DML operations allowed with multiple underline tables
  2. A View becomes invalid when the base tables are dropped
  3. View is database objects therefore it adds space
  4. Adds complexity during troubleshooting 

Synonym

Q: What is a synonym? A Synonym is an alternative name for database objects such as Tables, Views ,Sequence, Stored Procedures and other other database objects. Synonym provides both data independence and location transparency. It permits application to function without modification regardless of which user owns the table or view.

Q: What are the types of Synonym? There are two types of Synonym. Public and Private Synonyms. Public is for anyone who has access to the database Instance and private synonym is for one who owns it. Public Synonyms are dangerous if not used properly because anyone has access to it therefore you need to be careful while creating public Synonym.

Q: What are the advantages of Synonym?
  1. Provides layer of abstractions on a referenced objects
  2. Makes the object name simple,clear, meaningful 
  3. Provides backward compatibility
  4. Provides good layer of security if used properly
Q: What are the disadvantages of Synonym?
  1. Referenced objects can be dropped without dropping Synonym. This caused the looping chain of Synonym
  2. You cannot create a synonym on top of Synonym
  3.  Makes things complicate while troubleshooting an issue
  4. Can create a security hole if not used properly.

Index

Q: Explain Database Index? Index is a database object which contains an entry of each record that appears in a Indexed column to provide faster access of data. Table and View both can have Indexes.

Q: How do you decide which column needs an Index? You will need index on
  1. A frequently searched column
  2. A query with join and where clause
Q: What are the uses of Index? It is used to expedite the record search, delete and update query if used correctly.

Q: What are the various type of Index?  The Index types depend on the DBMS types. Oracle has local and global Index. SQL Server has Clustered and non Clustered Index.

Q: Can you force database to use an Index? You can use hint or similar command to force Index use in a query but that is not recommended. What is it not recommended? When the database engine ignores the Index use, there is a reason for it and you are required to find out the cause not force it. Depending on the table data size, data type and query use, sometime a full table scan might be better then using Index use.

Constraints

Q: Explain Database Constraints? A rule that restricts the values in a database to maintain the valid set of data. Most database system has six types of constraints.

Q: Explain the types of Constraints?
  1. A NOT NULL Constraint prohibits a database value from being NULL.
  2. A UNIQUE Constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be NULL. Unique is case sensitive meaning the values ‘ABC’ and ‘abc’ are accepted. (No duplicates, case sensitive, and  NULL Allowed)
  3. A PRIMARY KEY Constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null. (Unique, indexed,  & Check NOT NULL )
  4. A FOREIGN KEY Constraint requires values in one table to match values in another table
  5. A CHECK Constraint requires a value in the database to comply with a specified condition.
  6. A REF column by definition references an object in another object type or in a relational table. A REF Constraint lets you further describe the relationship between the REF column and the object it references
Q: 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. Most tables should have a primary key and each table can have only one PK.

Q: What is the difference between Primary Key and Unique key? Even though both Primary Key and Unique Key enforce uniqueness on the column however, there are few differences.

Primary Key:
  1. It doesn't allow Null values
  2. By default, it adds Clustered Index (SQL Server only)
  3. One Primary Key per table
Unique Key:
  1. It allows Null value.
  2. By default, it adds non Clustered Index (SQL Server only)
  3. Can have one or more Unique Key column
Q: What is referential integrity? Referential Integrity ensures the relationship between tables remains consistent among a parent and a child tables.

Sequence

Q: What is a Sequence? A Sequence is a sequence number generator. Sequence is used in a table to automatically populate primary keys values, order number, employee number etc. Oracle 11g and prior version use sequence to generate auto increment column while other DBMS will allow you to define identity or auto increment column.

CTAS

Q: Explain CTAS? CTAS stands for Create Table As Select. CTAS quickly lets your create a new table modeled after a table that already exist in your database.  CTAS  is commonly used when you need to quickly run some test command or to set up a lab in order to run some test cases. DBA and developer use CTAS to make a backup of a table data before they alter the production table or data. You can copy it back (rollback the change) just in case the change didn't go as planned.

CTAS will let you copy table structure along with data or just table structure only. Remember! it does not copy the table constrains. CTAS example:

Copy Table Only:
--Table only 
SELECT firstname, 
       lastname 
INTO   testtable --New table 
FROM   person.contact -- existing table 
WHERE  1 = 2;

Copy Table & Data:
--Table only 
SELECT firstname, 
       lastname 
INTO   testtable --New table 
FROM   person.contact -- existing table

Wild Cards

Q: Explain wild cards? Wildcard character are used with the SQL LIKE operator which are used to search data within a table.
  1. %: A substitute for  zero of More character
  2. _ : A substitute for a single character
  3. [charlist]: Set and ranges of characters to match
  4. [^charlist] or [!charlist]: Matches only a character NOT specified within the brackets
SELECT salary
    FROM employees
    WHERE last_name LIKE 'R%';  -- Last name begins with R

SELECT salary
    FROM employees
    WHERE last_name LIKE '%L_';  -- Last name with second last character is L

SELECT salary
    FROM employees
    WHERE last_name LIKE '[blp]%'; -- Last name begins with p, l or p

SELECT salary
    FROM employees
    WHERE last_name LIKE '[1blp]%'; -- Last name that does NOT begins with p, l or p

SQL Joins

Q: What are the types SQL Join? Explain each join types. A join clause combines rows from two or more base tables on a common field between them.

 Inner Join: Return all rows when there is at least one match in both tables.
Example:

Left Join: Also known as Left Outer Join which returns all rows from the left table and the matched rows from the right table.
Example:

Right Join: This join is also know as Right Outer Join which returns all the rows from the right table and the matched rows from the left table.
Example:

Full Join: Also know as Full Outer Join which returns all rows from the let table and from the right table. In simple it can be best explained as Right Join + Left Join = Full Join
Example:

SET Operators [Union, Union All, Intersect, Minus/Expect]

Q: Explain  Set Operators in SQL? UNION: SQL UNION is a set operator that can be used in SQL query when you want to combine the result set from two of more select statements or similar tables. The output of UNION will ignore the duplicates or in other word, it returns distinct rows by comparing the results of two or more queries.

SQL Syntax: 
SELECT  
FROM   TABLE 1 
WHERE  condition 
UNION 
SELECT  
FROM   TABLE 2 
WHERE  condition 
UNION 
SELECT  
FROM   TABLE 3 
WHERE  condition

UNION ALL: SQL UNION ALL is a set operator that can be used in SQL Query when you want to combine the result set from two or more select statements. The output of UNION ALL will NOT ignore the duplicates.

SQL Syntax: 
SELECT  
FROM   TABLE 2 
WHERE  condition 
UNION ALL 
SELECT  
FROM   TABLE 3 
WHERE  condition

INTERSECT: SQL INTERSECTION returns distinct rows that are in both the select statements.

SQL Syntax: 
SELECT product_id 
FROM   inventories 
INTERSECT
SELECT product_id 
FROM   order_items;

MINUS: The MINUS operator returns only unique rows returned by the first query but not on the second one

SQL Syntax: 
SELECT product_id 
FROM   inventories 
MINUS 
SELECT product_id 
FROM   order_items;

EXCEPT: The EXCEPT operator is similar to MINUS operator. SQL Server uses EXCEPT and other DBMS like ORACLE and My SQL use EXCEPT.

SQL Syntax: SQL Server only 
SELECT product_id 
FROM   inventories 
EXCEPT
SELECT product_id 
FROM   order_items;

Q: What is the difference between Join and an Union? A join combines two or more table using a common field. Union combines two or more tables irrespective of common field.

Q: What is the difference between UNION and UNION ALL? Union eliminates the duplicates row where as Union All displays everything regardless of duplicate. Union All operation is faster compared to UNION because it has sort and eliminate duplicate

SQL MISC

Q: What is the difference between a HAVING and a WHERE Clause?  They both are used in SQL to specify the search condition.  HAVING can only be used with a Select statement and is typically used with GROUP BY clause. 

Q: How many types of relationships exist in database design? There are three major relationship types in database design
  1. One-to-One 
  2. One-to-Many  
  3. Many-to Many
Q: When do you use LIKE operator? The like conditions specify a test involving pattern matching. See example below on how you use LIKE operator;

SQL Syntax: 
SELECT salary
    FROM employees
    WHERE last_name LIKE 'R%';  -- Last name begins with R

SELECT salary
    FROM employees
    WHERE last_name LIKE '%Z'; -- Last name ends with Z

SELECT salary
    FROM employees
    WHERE last_name LIKE '%NI%'; --Last name that contains NI

Q: What is Order By Clause? Logically sorts your column data output to ascending or descending order. By default most database sorts the data in an ascending order, you will need to specify the order if you need something other then ascending.

SQL Syntax: 
SELECT salary
    FROM employees
ORDER by salary DESC; --Sorts salary in a descending order

 SELECT salary
    FROM employees
ORDER by salary ASC;  --Sorts salary in a ascending order

Q: What does a Group By Clause do? The group by clause is used in collaboration with the SELECT statement to arrange identical data into groups

SQL Syntax: 
SELECT name,
       SUM(salary)
FROM   customers
GROUP  BY name;

What is SQL “IN” Clause? If you have a list of values in order to fetch rows from a table, you will need a IN SQL operator. Remember the value you can add under IN clause is limited to 1000 for Oracle and many thousands on SQL Server. The limit depends on database types.
SQL Syntax: 
Select Name from Employee
Where Name IN ( "Prabin", "Aakash", "Jeremy");

Q: What is the default sort order? The default sort operator is ascending. You must strictly mention if you want your data sort in descending order.

Q: What is a sub-Query? Explain the properties of sub-query.

Properties of Sub-Query

Q: What are Aggregate Functions? SQL aggregate functions returns a single calculated value from a column.  AVG, COUNT, MAX, MIN, SUM are some of the examples of Aggregate function.

Q:What are Scalar functions? Scalar functions returns a single value base on the input value. UCASE, LCASE, MID, LEN, ROUND, etc are some of the example of Scalar Functions.

Q: Explain select Into statement? This "Select Into" enables a query result to be stored in a variables, files or in a table.

SQL Syntax: 
INSERT INTO emp_prod
            (empid,
             first,
             last,
             phone)
SELECT empid,
       firstname,
       lastname,
       phone
FROM   emp_dev
WHERE  dept = 'CS';

Q: What is SQL Injection? A code injection technique used to attack data drive applications where malicious SQL statements are inserted to dump data back to the attacker or modifies the table data.

Q: What is a data warehousing? A data warehousing is a relational database designed to query and analysis rather than for transaction processing. It stores history or archived data from OLTP database.

Q: What is auto increment or identity column? Auto increment or identity column makes a column data generate automatically. It is normally used for generating PK, order number, employee number etc.

Q: What is the parameter substitution symbols used with insert into command?  &

Q: What are the various uses of database triggers?  A trigger is a stored program which executed automatically or fired when some events occurs. Triggers can be created for DML, DDL or with database server commands like startup, shutdown, logon etc.

Q: What are the benefit of using triggers? 
  1. Generating some derived column values automatically.
  2. Enforcing referential integrity
  3. Event logging and storing information on table access
  4. Auditing
  5. Synchronous replication of tables
  6. Imposing security authorization
  7. Preventing invalid transactions
Q: What are two methods of retrieving SQL? Select & the use of Cursor

Q: Difference between Stored Procedures Vs Functions.
  1. Functions must return value, in stored procedures it is optional
  2. Functions can be called from a Procedures whereas Procedures cannot be called from Functions
  3. Procedures allows SELECT, INSERT, UPDATE, DELETE statements whereas FUNCTIONS allows only SELECT statement.
Q: What is a cursor? A cursor is a pointer used to fetch rows from a result set.

These are questions that are database independent and applies to most relational database management systems. If you are looking to enhance your MySQL skills, deskbright has the best SQL resources for both begineer and expert SQL user.

5 comments

Subscribe to: Posts ( Atom )
Powered by Blogger.