SQl interview Questions

What is the difference between a function and a procedure?

- Function should always return a value whereas for a procedure it's not mandatory to return a value. - Function can be called from a SELECT query whereas procedure cannot be called from a SELECT query. - Function is generally used to perform some calculation and return a result. Whereas procedure is generally used to implement some business logic.

Explain a many-to-many relationship

A many-to-many relationship occurs when multiple records in one table are related to multiple records in another table. For example, products and suppliers: one supplier may deliver one or many products and at the same time, the company may order one product from one or many suppliers. The relationship between the Product entity and Order entity is many-to-many, as one product may be in many orders and many orders may contain the same product. Relational databases don't support direct many-to-many relationships between two tables. Then, how to implement many-to-many relationships in SQL? To create a many-to-many relationship in a database, you'll need to create a third table to connect the other two. This new table (also known as a linking, joining, bridging, or junction table) will contain the primary key columns of the two tables you want to relate and will serve as an intermediate table between them.

Explain a one-to-many relationship

A one-to-many relationship occurs when one record in table 1 is related to one or more records in table 2. However, one record in table 2 cannot be related to more than one record in table 1. We can come up with hundreds of examples of such relations: pages and the book they belong to, pupils and their class, orders and the customer who placed them, etc. How to join tables with one-to-many relationship in SQL? INNER JOINs are considered to be the most effective way to combine data from two tables that have one-to-many relationship. Let's query two SQL tables having one-to-many relationship. How to implement one-to-many relationships when designing a database: 1. Create two tables (table 1 and table 2) with their own primary keys. 2. Add a foreign key on a column in table 1 based on the primary key of table 2. This will mean that table 1 can have one or more records related to a single record in table 2.

Explain one-to-one relationship.

A one-to-one relationship in a database occurs when each row in table 1 has only one related row in table 2. For example, a department may have only one head manager, a husband — only one wife, an employee — one company car, etc. How to create one-to-one relationship in SQL Server? For this, you will need to create two tables, then define a simple primary foreign key relationship between them, and set the foreign key column to be unique.

SUBQUERIES

A select Query inside another select query, also called inner query. Subquery is executed first.

Aggregate functions

Calculations such as: MIN - find the minimum value MAX - find the maximum value AVG - find the average value SUM - sum of given values COUNT - count all values

What is DCL?

DCL stands for Data Control Language. DCL includes GRANT and REVOKE statements. GRANT statements are used to provide access privileges to a database object to any database or schema. REVOKE statements are used to remove access privileges from a database object from any database or schema.

What is DDL?

DDL stands for Data Definition Language. They include CREATE, DROP, ALTER and TRUNCATE statements. DDL statements are used to create, remove or modify database objects like table. You do not need to commit the changes after running DDL commands. CREATE statement can be used to create any database objects like tables, views, functions, procedures, triggers etc. DROP statement can be used to remove any database objects like tables, views, functions, procedures, triggers etc. ALTER statement can be used to modify the structure of a database objects. TRUNCATE statement can be used to remove all the data from a table at once.

Difference between DELETE, TRUNCATE and DROP

DELETE - removes few or all records from a table TRUNCATE - removes all recoreds from a table DROP - deletes a table

Difference between DISTINCT and GROUP BY

DISTINCT - returns only unique values (those that are not repeated) GROUP BY - used to perform some aggregation (MIN, MAX, COUNT, SUM, AVG)

What is DML?

DML stands for Data Manipulation Language. DML includes INSERT, UPDATE, DELETE and MERGE statements. DML statements are used to add, remove or modify data from database tables. It is mandatory to run the COMMIT command after running a DML statement so as to save the changes to the database (some tools may have auto commit on so you don't have to manually run the commit command). INSERT statement will add rows or records to a table. UPDATE statement will modify the data in the table. DELETE statement will remove one or multiple rows from a table. MERGE statement will either do an update or insert to a table based on the available data. If the data is present then it does an update. If data not present then merge will do an insert.

What is DQL?

DQL stands for Data Query Language. It includes only the SELECT statement. SELECT statement is used to fetch and view data from the database.

When can a function NOT be called from SELECT query?

If the function includes DML operations like INSERT, UPDATE, DELETE etc then it cannot be called from a SELECT query. Because SELECT statement cannot change the state of the database.

What are indexes in SQL?

Index is a database object which is applied on one or more columns of a table. When a column is indexed, database xreates a pointer to each value stored in that column. This improves the query execution significantly. The index can be defined as the way to retrieve the data more quickly. We can define indexes using CREATE statements. Syntax: CREATE INDEX index_name ON table_name (column_name) Further, we can also create Unique Index using following syntax: CREATE UNIQUE INDEX index_name ON table_name (column_name)

What is a SYNONYM?

It's just an alias or an alternate name that you can provide to any database objects such as tables, views, sequences, procedures etc. Synonym is created for a single database object whereas view can be created on a query where the query may have been formed by multiple tables.

Modulo

MOD(15, 4) Result: 3

What is MERGE statement?

Merge is part of the DML commands in SQL which can be used either perform INSERT or UPDATE based on the data in the respective table. If the desired data is present then merge will update the records. If desired data is not present then merge will insert the records. Sample merge statement is shown below. Here if the managers and directors table have matching records based the ID field then UPDATE command will be run else if there are no matching records then INSERT statement will be executed. MERGE INTO managers m USING directors d ON (m.id = d.id) WHEN MATCHED THEN UPDATE SET name = 'TEST' WHEN NOT MATCHED THEN INSERT VALUES (d.id, d.name, 0);

Difference between PRIMARY, UNIQUE and FOREIGN KEY.

PRIMARY KEY - always has unique/distinct value - one table can only have one primary key - it can be created on one column or a group of colums UNIQUE KEY - always has unique/distinct value - NULL values are allowed in a column which has unique key constraint FOREIGN KEY - used to create a master child kind of relationship between two tables - when we make a column in a table as foreign key, this column will then have to be referenced from another column from some other table

Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

SELECT COUNT(*) - COUNT(DISTINCT s.CITY) FROM STATION s;

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.

SELECT DISTINCT s.CITY FROM STATION s WHERE MOD(ID, 2) = 0;

Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters. Your result cannot contain duplicates.

SELECT distinct(city) from station where substr(city, 1, 1) in('A', 'E', 'I', 'O', 'U') and substr(city, -1, 1) in ('a', 'e', 'i', 'o', 'u');

Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.

SELECT distinct(city) from station where substr(city, 1, 1) not in('A', 'E', 'I', 'O', 'U');

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Select country.continent, floor(AVG(city.population)) from country join city on city.countrycode = country.code group by country.continent;

Query the average population for all cities in CITY, rounded down to the nearest integer.

Select floor(avg(population)) from city;

Query the difference between the maximum and minimum populations in CITY.

Select max(population) - min(population) from city;

Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S). Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format: There are a total of [occupation_count] [occupation]s. where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

Select name||'('||substr(occupation, 1, 1)||')' from occupations order by name; Select 'There are a total of '||count(occupation)||' '||lower(occupation)||'s.' from occupations group by occupation order by count(occupation) asc, occupation asc;

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345 Round your answer to decimal places.

Select round(long_w, 4) from station where lat_n = (select max(lat_n) from station where lat_n < 137.2345);

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Select sum(city.population) from city inner join country on city.countrycode = country.code where country.continent = 'Asia';

Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345. Truncate your answer to 4 decimal places.

Select trunc(max(lat_n), 4) from station where lat_n < 137.2345 order by lat_n desc;

CASE

Similiar to IF ELSE statement CASE name WHEN 'John' THEN 'Name John' WHEN 'Steve' THEN 'Name Steve' ELSE 'Unknown' END

What is TCL?

TCL stands for Transaction Control Language. TCL includes COMMIT, ROLLBACK and SAVEPOINT. COMMIT statement will permanently save any open transactions in the current session to the database. By transaction, I mean any changes done to any database table using any of the DML statements like INSERT, UPDATE, DELETE and MERGE. ROLLBACK statement will remove (unsave) any open transactions in the current session to the database. So all un committed transactions in the current session will be lost. SAVEPOINT statement can be used to create a specific pointer in your session and provide a name to this pointer. You can then either rollback or commit transactions only until this point (savepoint name) rather than committing or rollbacking all the transaction in the session.

What is a TRIGGER?

Trigger is a database object which is similar to a stored procedure which will automatically get invoked or executed when the specified event occurs in the database. The most common type of triggers are DML triggers, DDL triggers and Database triggers (also referred as Logon triggers). DML triggers are invoked when a DML operation (INSERT, UPDATE, DELETE) occurs on the respective table (table on which the trigger was created). Trigger can be configured to invoke either before the DM operation or after the DML operation. DDL triggers are invoked when a DDL operation (CREATE, ALTER, DROP) occurs on the respective table (table on which the trigger was created). Database trigger is invoked when the database session is established or shut down.

UNION operator + rules

Used to combine two different SQL queries. Duplicates are not returned. Rules: 1. both queries must return the same number of columns 2. columns in both quesries must be in the same order 3. data type of all columnes in both queries must be the same

What is a VIEW?

View is a database object which is created based on a SQL Query. It's like giving a name to the results returned from a SQL Query and storing it in the database as a view. If the query result changes then the data in view also changes. View is directly linked to the SQL Query over which it was created.

Difference between WHERE and HAVING

WHERE make selections on data in the tables and HAVING makes selections on groups of records.

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

select * from(select distinct city,length(city) from station order by length(city) asc,city asc) where rownum=1 union select * from(select distinct city,length(city) from station order by length(city) desc,city desc) where rownum=1;

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

select distinct(CITY) from STATION where city like 'A%' OR city like 'E%' OR city like 'I%' OR city like 'O%' OR city like 'U%';

Query the following two values from the STATION table: The sum of all values in LAT_N rounded to a scale of decimal places. The sum of all values in LONG_W rounded to a scale of decimal places.

select round(sum(lat_n), 2), round(sum(long_w), 2) from station;