SQL Test 1

How to select random rows from a table?

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

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.

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.

Which TCP/IP port does SQL Server run?

By default SQL Server runs on port 1433.

How we can avoid duplicating records in a query?

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

Define COMMIT ?

COMMIT saves all changes made by DML statements.

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- Right Join Outer Join Full Join Cross Join Self Join.

How to select all records from the table?

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

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.

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.

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.

What is CTE?

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

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.

What is the Subquery ?

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

Do View contain Data?

No, Views are virtual structure.

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.

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.

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.

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.

Explain DML and DDL?

DML 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.

What is SQL comments?

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

What does SQL stand for?

SQL stands for Structured Query Language

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.

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)

What do you mean by ROWID ?

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

What is schema?

A schema is collection of database objects of a User.

What is a stored procedure?

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

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.

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.

Define a temp table?

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

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.

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.

What are the advantages of Views?

Advantages of Views: 1. Views restrict access to the data because the view can display selective columns from the table. 2. 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.

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.

How do you add a column to a table?

To add another column in the table following command has been used.

What is the syntax to add record to a table?

To add record in a table INSERT syntax is used.

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;

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>

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 the properties of a transaction?

Generally these properties are referred as ACID properties. They are: Atomicity Consistency Isolation Durability.

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.

What is a composite primary key?

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

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.

List the various privileges that a user can grant to another user?

SELECT, CONNECT, RESOURCES.

What is Collation?

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

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.

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)

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;

Can a View based on another View?

Yes, A View is based on another View.

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.