SQL interview Questions

Can a table have more than one foreign key defined?

A table can have any number of foreign keys defined. It can have only one primary key defined.

Q#30. What is difference between UNIQUE and PRIMARY KEY constraints?

A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. Primary key cannot contain Null values whereas Unique key can contain Null values.

Q#12. What are the possible values for BOOLEAN data field.

For a BOOLEAN data field two values are possible: -1(true) and 0(false).

What are foreign keys?

Foreign key field is a field that links one table to another table's primary or foreign key.

Q#26. What are the properties of a transaction?

Generally these properties are referred as ACID properties. They are: 1. Atomicity 2. Consistency 3. Isolation 4. Durability.

Q#28. Define UNION, MINUS, UNION ALL, INTERSECT ?

MINUS - returns all distinct rows selected by the first query but not by the second. UNION - returns all distinct rows selected by either query UNION ALL - returns all rows selected by either query, including all duplicates. INTERSECT - returns all distinct rows selected by both queries.

Q#20. Explain DML and DDL?

ML stands for Data Manipulation Language. INSERT, UPDATE and DELETE are DML statements. DDL stands for Data Definition Language. CREATE ,ALTER, DROP, RENAME are DDL statements.

Q#46. Do View contain Data?

No, Views are virtual structure.

Q#40. Explain the difference between Rename and Alias?

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

Q#10. What is CHECK Constraint?

A CHECK constraint is used to limit the values or type of data that can be stored in a column. They are used to enforce domain integrity.

Q#50. What is CTE?

A CTE or common table expression is an expression which contains temporary result set which is defined in a SQL statement.

Q#8. What is a primary key?

A Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.

Q#33. What is the Subquery ?

A Subquery is sub set of select statements whose return values are used in filtering conditions of the main query.

Q#18. Which TCP/IP port does SQL Server run?

By default SQL Server runs on port 1433.

Q#44. What is schema?

A schema is collection of database objects of a User.

Q#13. What is a stored procedure?

A stored procedure is a set of SQL queries which can take input and send back output.

Q#45. What is Table ?

A table is the basic unit of data storage in the database management system. Table data is stored in rows and columns.

Q#38. Define a temp table?

A temp table is a temporary storage structure to store the data temporarily.

Q#29. What is a transaction?

A transaction is a sequence of code that runs against a database. It takes database from one consistent state to another.

Q#41. What is a View?

A view is a virtual table which contains data from one or more tables. Views restrict data access of table by selecting only required values and make complex queries easy.

Q. How do you change value of the field?

A. UPDATE EMP_table set number = 200 where item_munber = 'CD'; update name_table set status = 'enable' where phone = '4161112222'; update SERVICE_table set REQUEST_DATE = to_date ('2006-03-04 09:29', 'yyyy-mm-dd hh24:MM') where phone = '4161112222';

Q. How do you add a column to a table?

ALTER TABLE Department ADD (AGE, NUMBER);

Q#42. What are the advantages of Views?

Advantages of Views: - Views restrict access to the data because the view can display selective columns from the table. - Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing.

You issue the following query: SELECT FirstName FROM StaffListWHERE FirstName LIKE '_A%' Which names would be returned by this query? Choose all that apply. Allen CLARK JACKSON David

Allen

Q#32. What is an Index ?

An Index is an special structure associated with a table speed up the performance of queries. Index can be created on one or more columns of a table.

Q#14. What is identity in SQL?

An identity column in the SQL automatically generates numeric values. We can defined a start and increment value of identity column.

Q. Which of the following statements are Data Manipulation Language commands? INSERT UPDATE GRANT TRUNCATE CREATE

Ans. A and B. The INSERT and UPDATE statements are Data Manipulation Language (DML) commands. GRANT is a Data Control Language (DCL) command. TRUNCATE and CREATE are Data Definition Language (DDL) commands

Q#48. What is difference between Having clause and Where clause?

Both specify a search condition but Having clause is used only with the SELECT statement and typically used with GROUP BY clause. If GROUP BY clause is not used then Having behaves like WHERE clause only.

Q#39. How we can avoid duplicating records in a query?

By using DISTINCT keyword duplicating records in a query can be avoided.

Q#7. Define COMMIT ?

COMMIT saves all changes made by DML statements.

Q#37. What is Case Function?

Case facilitates if-then-else type of logic in SQL. It evaluates a list of conditions and returns one of multiple possible result expressions.

Q#25. Difference between TRUNCATE, DELETE and DROP commands?

DELETE removes some or all rows from a table based on the condition. It can be rolled back. TRUNCATE removes ALL rows from a table by de-allocating the memory pages. The operation cannot be rolled back DROP command removes a table from the database completely.

What is DML and DDL?

DML and DDL are subsets of SQL. DML stands for Data Manipulation Language and DDL - Data Definition Language. DML consist of INSERT, UPDATE and DELETE DDL commands CREATE TABLE, ALTER TABLE, DROP TABLE, RENAME TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX. CREATE/ALTER/DROP VIEW

Q#6. Define SQL Delete statement.

Delete is used to delete a row or rows from a table based on the specified condition. Basic syntax is as follows: DELETE FROM table_name WHERE <Condition>

How do you add record to a table?

INSERT into table_name VALUES ('ALEX', 33 , 'M');

Q#49. What is difference between Local and Global temporary table?

If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the db but its rows disappears when the connection is closed.

Q#27. What do you mean by ROWID ?

It's a 18 character long pseudo column attached with each row of a table.

Q. What is a join?

Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one "joined" collection of data.

Q#3. Define join and name different type of joins?

Join keyword is used to fetch data from related two or more tables. It returns rows where there is at least one match in both the tables included in join. Read more here. Type of joins are- 1. Right Join 2. Outer Join 3.Full Join 4.Cross Join 5. Self Join.

Q#22. Give the order of SQL SELECT ?

Order of SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.

Q#31. What is a composite primary key?

Primary key created on more than one column is called composite primary key.

Q#34. What do you mean by query optimization?

Query optimization is a process in which database system compares different query strategies and select the query with the least cost.

Write a SQL SELECT query that only returns each city only once from Students table? Do you need to order this list with an ORDER BY clause?

SELECT DISTINCT City FROM Students;

Write SQL SELECT query that returns the first and last name of each instructor, the Salary, and gives each of them a number.

SELECT FirstName, LastName, Salary, ROWNUM FROM Instructors;

Q#23. Suppose a Student column has two columns, Name and Marks. How to get name and marks of top three students.

SELECT Name, Marks FROM Student s1 where 3 <= (SELECT COUNT(*) FROM Students s2 WHERE s1.marks = s2.marks)

Q#43. List the various privileges that a user can grant to another user?

SELECT, CONNECT, RESOURCES.

Describe SQL comments.

SQL comments are introduced by two consecutive hyphens (--) and ended by the end of the line.

Q#24. What is SQL comments?

SQL comments can be put by two consecutive hyphens (-).

Q#1. What does SQL stand for?

SQL stands for Structured Query Language.

What does COMMIT do?

Saving all changes made by DML statements

Q. How do you select all records from the table?

Select * from table_name;

Q#35. What is Collation?

Set of rules that defines how data is stored, how case sensitivity and Kana character can be treated etc.

Q#36. What is Referential Integrity?

Set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

Difference between TRUNCATE, DELETE and DROP commands?

The DELETE command is used to remove 'some or all rows from a table. TRUNCATE removes ALL rows from a table. The operation cannot be rolled back The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.

Describe how NULLs work in SQL?

The NULL is how SQL handles missing values. Arithmetic operation with NULL in SQL will return a NULL.

What is a primary key?

The column (columns) that has completely unique data throughout the table is known as the primary key field.

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

The highest value that can be stored in a BYTE field is 255. or from -128 to 127. Byte is a set of Bits that represent a single character. Usually there are 8 Bits in a Byte, sometimes more, depending on how the measurement is being made. Each Char requires one byte of memory and can have a value from 0 to 255 (or 0 to 11111111 in binary).

What is the main role of a primary key in a table?

The main role of a primary key in a data table is to maintain the internal integrity of a data table.

What is Normalization?

The process of table design is called normalization.

Q#15. What is Normalization?

The process of table design to minimize the data redundancy is called normalization. We need to divide a database into two or more table and define relationships between them.

Q. List all the possible values that can be stored in a BOOLEAN data field.

There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false).

How do you add a column to a table?

To add another column in the table following command has been used. ALTER TABLE table_name ADD (column_name varchar(number of character));

Q#4. What is the syntax to add record to a table?

To add record in a table INSERT syntax is used. Ex: INSERT into table_name VALUES (value1, value2..);

Q#19. Write a SQL SELECT query that only returns each name only once from a table?

To get the each name only once, we need to use the DISTINCT keyword. SELECT DISTINCT name FROM table_name;

Q#16. What is Trigger?

Trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table)

What is Trigger?

Trigger will execute a block of procedural code against the database when a table event occurs. A2. A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, in this case the trigger has been activated.

Q#17. How to select random rows from a table?

Using SAMPLE clause we can select random rows. Example: SELECT * FROM table_name SAMPLE(10);

Q#9. What are foreign keys?

When a one table's primary key field is added to related tables in order to create the common field which relates the two tables, it called a foreign key in other tables. Foreign Key constraints enforce referential integrity.

Q#21. Can we rename a column in the output of SQL query?

Yes using the following syntax we can do this. SELECT column_name AS new_name FROM table_name;

Q#47. Can a View based on another View?

Yes, A View is based on another View.

Q#11. Is it possible for a table to have more than one foreign key?

Yes, a table can have many foreign keys and only one primary key.

Is the WHERE clause must appear always before the GROUP BY clause in SQL SELECT ?

Yes. The proper order for SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory.

Can one select a random collection of rows from a table?

Yes. Using SAMPLE clause. Example: SELECT * FROM EMPLOYEES SAMPLE(10); 10% of rows selected randomly will be returned.

Q#2. How to select all records from the table?

o select all the records from the table we need to use following syntax: Select * from table_name;