SQL Interview Questions Flashcards
source: https://www.edureka.co/blog/interview-questions/sql-interview-questions
ACID
ACID (Atomicity, Consistency, Isolation, Durability) - It is used to ensure that the data transactions are processed reliably in a database system.
- Atomicity - refers to the transactions that are completely done or failed where transaction refers to a single logical operation of a data. It means if one part of any transaction fails, the entire transaction fails and the database state is left unchanged.
- Consistency - ensures that the data must meet all the validation rules. In simple words, you can say that your transaction never leaves the database without completing its state.
- Isolation - The main goal of isolation is concurrency control.
- Durability - means that if a transaction has been committed, it will occur whatever may come in between such as power loss, crash or any sort of error.
Aggregate vs Scalar functions
Aggregate functions are used to evaluate mathematical calculation and returns a single value. These calculations are done from the columns in a table. For example- MAX(), COUNT(), SUM().
Scalar functions return a single value based on the input value. For example – UPPER(), NOW().
ALIAS
- ALIAS name can be given to any table or a column.
- This alias name can be referred in WHERE clause to identify a particular table or a column.
Authentication modes. How can it be changed?
Windows mode and Mixed Mode
- Click Start> Programs> Microsoft SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
- Then select the server from the Tools menu.
- Select SQL Server Configuration Properties, and choose the Security page.
Auto Increment
- automatically generate unique numbers when inserting new record to table.
- usually use auto increment column as primary key
- automatically increase value by 1 for each record, use parameters to change starting and increment values
- AUTO INCREMENT keyword can be used in Oracle and IDENTITY keyword can be used in SQL SERVER.
BETWEEN vs IN
- BETWEEN is a range
- IN check if value exist in given set
case manipulation functions
* LOWER: takes a string as an argument and returns it by converting it into lower case. LOWER(‘string’)
* UPPER: takes a string as an argument and returns it by converting it into uppercase. UPPER(‘string’)
* INITCAP: returns the string with the first letter in uppercase and rest of the letters in lowercase. INITCAP(‘string’)
CHAR vs VARCHAR2
- both character datatypes
- CHAR uses a fixed length (“abc” -> CHAR(10) -> “abc “ (10 character)
- VARCHAR2 can store any length up to the limit (“abc” -> VARCHAR(10) -> “abc” (3 character)
CLAUSE in SQL
- helps to filter/limit the rows from the entire set of records.
- WHERE, HAVING clause.
Clustered vs Non-clustered index
Clustered Index: * Physically stored the rows on the dick in the same order as the index * there can only be 1 cluster * faster to read from clustered index Non-Clustered Index: * a list that points to physical rows * can have many * takes time to write therefore slower than clustered index
Collation
Set of rules that determine how data can be sorted as well as compared.
Character data is sorted using the rules that define the correct character sequence along with options for specifying case-sensitivity, character width etc.
types of Collation Sensitivity
- Case Sensitivity: A and a and B and b.
- Kana Sensitivity: Japanese Kana characters.
- Width Sensitivity: Single byte character and double-byte character.
- Accent Sensitivity.
How can you fetch common records from two tables?
- fetch common records from two tables using INTERSECT
- example:
SELECT studentID FROM student
INTERSECT
SELECT StudentID FROM Exam
What are the various levels of constraints?
- column level constraint
- table level constraint
Constraints
- use to specify limits/rules when creating or altering tables
- list of constraints:
- NOT NULL
- CHECK
- DEFAULT
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
CROSS JOIN vs NATURAL JOIN
- CROSS JOIN - returns cross product of two tables
- JOIN - returns data from both table based on common column
Current date
* SELECT CURRENT_DATE;
Data Integrity
* refers to accuracy and consistency of data. * constraints helps with integrity when entered * business rules reinforcement
Datawarehouse
- Central repository of data where the data is assembled from multiple sources of information.
- Data are consolidated, transformed and made available for the mining as well as online processing.
- Warehouse data also have a subset of data called Data Marts.
What is DBMS? What are its different types?
- Database Management System (DBMS) is a software allowing interaction with the data. (PostGresSql, MS SQL, Oracle, etc)
- 2 types of DBMS:
- Relational Database Management System (RDBMS) - structured relational tables (MySQL, SQL etc)
- Non-Relational Database Management System - unstructured database (Mongo)
DELETE vs TRUNCATE
DELETE:
- delete a row in a table
- can rollback data after deletion
- uses DML command (Data Manipulation Language)
- slower than truncate
TRUNCATE:
- delete all rows in a table
- cannot rollback
- uses DDL command (Data Definition Language)
- faster than delete
Denormalization
- Opposite of normalization where it combines smaller tables into larger one.
- it causes redundancy
select unique records from a table
You can select unique records from a table by using the DISTINCT keyword
Example:
SELECT DISTINCT studentID
FROM Student
DROP vs TRUNCATE
DROP
- * Deletes the table
- * cannot be rollback
TRUNCATE
- * Removes all the rows from the table
List the ways in which Dynamic SQL can be executed?
- Write a query with parameters.
- Using EXEC.
- Using sp_executesql.
Entities and Relationships
- Entities are tables or data stored about a people, places or things (customer table, bank transaction)
- Relationships are links between entities (customer table and bank transaction are linked by customer id field)
FOREIGN key
* link between two tables * references a primary key in a different table
Why are SQL functions used?
SQL functions are used for the following purposes:
- To perform some calculations on the data
- To modify individual data items
- To manipulate the output
- To format dates and numbers
- To convert the data types
Types of User-defined functions
- Scalar Functions - return unit
- Inline Table-valued functions - returns table
- Multi-statement valued functions - returns table
GROUP BY
* needs an aggregate function: AVG, COUNT, MAX, MIN, SUM, VARIANCE, FIRST, LAST.
HAVING vs WHERE
* HAVING Clause is only used with the GROUP BY function * WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query
Index
- performance tuning method for faster data retrieval/reading
Different types of index
Unique index:
- no duplicate values (primary key for example)
Clustered Index:
- reorders the physical table based on key values
- 1 clustered index per table
Non-Clustered Index:
- doesn’t reorder the physical table
- maintain logical order of data
- can have many non-clustered indices
JOINs
- Joins is a way of combining row from 2+ tables/sources into 1 place based on common/related column
- types of joins:
- INNER JOIN - returns what both tables have in common
- LEFT JOIN - inner join + left table data
- RIGHT JOIN - inner join + right table data
- FULL JOIN - all data from both tables
JOINS types
- INNER JOIN - returns what both tables have in common
- LEFT JOIN - inner join + left table data
- RIGHT JOIN - inner join + right table data
- FULL JOIN - all data from both tables
MERGE
- Allows conditional update or insertion and deletion of data in a table.
- It performs an UPDATE if a row exists, or an INSERT if the row does not exist.
- Format:
MERGE target_table USING source_table
ON merge_condition
WHEN MATCHED
THEN update_statement
WHEN NOT MATCHED
THEN insert_statement
WHEN NOT MATCHED BY SOURCE
THEN DELETE; - example:
MERGE sales.category t USING sales.category_staging s
ON (s.category_id = t.category_id)
WHEN MATCHED
THEN UPDATE SET
t.category_name = s.category_name, t.amount = s.amount
WHEN NOT MATCHED BY TARGET
THEN INSERT (category_id, category_name, amount)
VALUES (s.category_id, s.category_name, s.amount)
WHEN NOT MATCHED BY SOURCE
THEN DELETE;
Normalization
Organize data to avoid duplicate and redundancy
- Better Database organization
- More Tables with smaller rows
- Efficient data access
- Greater Flexibility for Queries
- Quickly find the information
- Easier to implement Security
- Allows easy modification
- Reduction of redundant and duplicate data
- More Compact Database
- Ensure Consistent data after modification
Normalization types
- 1st Normal Form (1NF) - 1 entry per cell, no multiple values
- 2nd Normal Form (2NF) - non-key columns are dependent on the primary key
- 3rd Normal Form (3NF) - Dependent solely on the primary key and no other non-key (supporting) column value.
NULL vs zero/space values
* NULL values means no data (unknown, unavailable, N/A) * zero is actually a number (so it’s a value and not null) * blank space is actually a character (so not null)
insert NULL values
- Implicitly by omitting column from column list.
- Explicitly by specifying NULL keyword in the VALUES clause
operator which is used in the query for pattern matching
LIKE operator is used for pattern matching
- % – It matches zero or more characters
- _ (Underscore) – it matches exactly one character
Operators
- Arithmetic (+ - / *)
- Logical (AND OR NOT)
- Comparison(> < !=)
different set operators
Union, Intersect or Minus operators.
Primary key
A constraint which is:
- can’t be null (null values not allowed)
- unique (no duplicate)
- can be a column or a collection of columns
- 1 per table
Recursive Stored Procedure
A stored procedure which calls by itself until it reaches some boundary condition.
Relationship
Relationships are links between entities (customer table and bank transaction are linked by customer id field)
Type of relationships:
- One to One Relationship.
- One to Many Relationship.
- Many to One Relationship.
- Many to Many Relationship.
COUNT records in a table
- SELECT * FROM table;
- SELECT COUNT(*)
FROM table;
How can you fetch alternate records from a table?
You can fetch alternate records i.e both odd and even row numbers.
For example- To display even numbers, use the following command:
SELECT studentId
FROM (Select rowno, studentId from student)
WHERE MOD(rowno,2)=0
SQL query to find the names of employees that begin with ‘A’
SELECT *
FROM Table_name
WHERE EmpName LIKE ‘A%’;
How can you fetch first 5 characters of the string?
SELECT SUBSTRING(StudentName,1,5) as studentname
FROM student
SQL query to get the third highest salary of an employee from employee_table
SELECT TOP 1 salary
FROM (SELECT TOP 3 salary FROM employee_table ORDER BY salary DESC) ORDER BY salary ASC;
What is the difference between SQL and MySQL?
- SQL is language (Structured Query Language)
- MySQL is RDBMS like SQL Server or PostGresSQL
SQL vs PL/SQL
SQL - Structured Query Language executing single CRUD command
PL/SQL - Procedural Language SQL from Oracle, full program capable of executing mulitple operations using loops and variables
What are the different subsets of SQL?
- DDL (Data Definition Language) - allows you database level operations such as CREATE, ALTER DELETE objects
- DML (Data Manipulation Language) - allows data level operations such as CRUD in the database
- DCL (Data Control Language) - allows/control access to database (handles permissions)
Stored Procedure
- Saved reusable sql statements
- think of it as saving you sql statement into a variable or a function(you can pass parameters to it) that you can use later on
List advantages and disadvantages of Stored Procedure
Advantages:
- reusable
- Reduces networks traffic
- Fast execution
- Better security to data
Disadvantages:
- utilizes more memory in the database server because it can only be executed in the database
STUFF vs REPLACE
STUFF
- replaces a specify substring with given length and position
REPLACE
- replaces all occurrences
Subquery
(query-ception!)
- It is a nested query, query inside another query
- they are executed before the main query
- can be in SELECT UPDATE WHERE,…
Type of Subqueries
2
- Correlated - data is from or referenced by main query
- Non-correlated - independent data from outside source, substituted in main query
Table vs Field
- Table - collection of data organized by rows and columns
- Field - a column in a table
Trigger
- special type of Stored Procedures that is executed when data/database modification happens
- Logon trigger (login into a db)
- DML trigger (INSERT UPDATE DELETE)
- DDL trigger (CREATE ALTER DROP)
UNIQUE Key
* a constraint on column(s) * no duplicate values * no null
Local vs Global variables
Local variables
- exist only inside the function
- not used or referred by any other function
Global variables
- variables which can be accessed throughout the program
- cannot be created whenever that function is called
View
- Virtual table made of subset data from table(s)
- Views have up-to-date data
What are Views used for?
- Restricting access to data.
- Making complex queries simple.
- Ensuring data independence.
- Providing different views of same data.
