SQL Flashcards
What is the difference between DELETE and TRUNCATE statements?
DELETE
- The delete command is used to delete a row in a table
- You can rollback data after using the delete statement
- It is a DML command
- It is slower than the truncate statement
TRUNCATE
- Truncate is used to delete all the rows from a table
- You cannot rollback data
- It is a DDL command
- It is faster
What are the different subsets of SQL?
DDL (Data Definition Language): Consists of the commands that can be used to change and modify the structure of a table
- CREATE
- ALTER
- DROP
- RENAME
- TRUNCATE
DML (Data Manipulation Language): Consists of the commands that deal with the manipulation of data present in the database
- INSERT
- UPDATE
- DELETE
DCL (Data Control Language): Includes the commands which deal with the rights, permissions, and other controls of the database system
- GRANT
- REVOKE
TCL (Transaction Control Language): Includes the commands which mainly deal with the transactions in a database
- COMMIT
- ROLLBACK
- SAVEPOINT
DQL (Data Query Language): Used to fetch data from the database
- SELECT
What are joins in SQL?
A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two table and retrieve data from there
Left Table: First table referenced
Right Table: Second table mentioned
Inner Join: Returns records that have matching values in both tables
Full Join: Returns all records when there is a single match in either the left or right table
Left Join: Returns all records from the left table, and the matching records from the right table
Right Join: Returns all records from the right table, and the matching records from the left table
What is the difference between CHAR and VARCHAR datatype in SQL?
CHAR is used for strings of a fixed length
- Ex: char(10) can only store 10 characters and will not be able to store a string of any other length
VARCHAR is used for character strings of variable length
- Ex: varchar(10) can store any length under 10
What is a Primary Key?
A single or group of fields or columns that can uniquely identify a row in a table
What are constraints?
Constraints are used to specify the limit on the data type of the table. It can be specified when the table is created or altered.
Not Null: Ensures that a NULL value cannot be stored in the column
Unique: This constraint makes sure that all the values in a column
are different
Check: This constraint ensures that all the values in a column satisfy
a specific condition
Default: This constraint consists of a set of default values for a
column when no value is specified
Index: This constraint is used to create and retrieve data from the
database very quickly
What is a Unique Key?
A unique key in SQL is the set of fields or columns of a table that helps us uniquely identify records. The unique key guarantees the uniqueness of the columns in the database. It is similar to the primary key but can accept a null value. A primary key is automatically a unique key. More than one unique key can exist in a table, while primary keys cannot
What is a Foreign Key?
- A column or combination of columns that is used to establish and enforce a link between the data in two tables.
- The foreign key in the child table references the primary key in the parent table
- The foreign key constraint prevents actions that would destroy links between the child and parent table
What is Data Integrity?
Accuracy and Consistency of data
What function would you use to display the current date?
SELECT GETDATE();
What is an Index?
Indexes are special tables used to increase the performance of data retrieval. This works like the index of a textbook, where you can use it to look up the topic of specific information and go straight to the page.
In this case, having an index for specific data can speed up the time it takes to use SELECT queries and WHERE clauses. They have no effect on the data as it is just a reference. Indexes take time to create and take up storage.
It is not wise to use indexes for small tables with a narrow range of values, or if the column has a large number of null values, or if the column is updated frequently because the indexes will also need to update alongside it.
What is the difference between the DROP and TRUNCATE commands?
DROP removes a table and cannot be rolled back from the database
- Ex: DROP object object_name;
TRUNCATE removes all rows from the table and cannot be rolled back into the database
- Ex: TRUNCATE TABLE table_name;
Explain the different types of Normalization
1NF - Each table cell should have a single value. So all records must be unique
2NF - Database should be 1NF and should depend on primary key column, partial dependencies placed in a separate table
3NF - Database should be in 2NF and must not have any transitive functional dependencies. Non primary key columns should not depend on non primary key columns
What is ACID?
Atomicity: All or none; Either the whole transaction goes through or
none of it does
Consistency: The saved data cannot violate the integrity of the
database
Isolation: Keeps the transactions separated from each other until
they’re finished
Durability: System failures or restarts do not affect committed
transactions
What is a Trigger in SQL?
Triggers in SQL are a special type of stored procedure that are defined to execute automatically in place or after data modifications. It allows you to execute a batch of code when an insert, update or any other query is executed against a specific table
Before Insert: Activated before data is inserted into the table
After Insert: Activated after date is inserted into the table
Before Update: Activated before data in the table is updated
After Update: Activated after the data in the table is updated
Before Delete: Activated before data is removed from the table
After Delete: Activated after data is removed from the table
Are NULL values the same as that of zero or a blank space?
A NULL value is not at all the same as that of a zero or blank space
NULL value represents a value which is unavailable, unknown, assigned or not applicable whereas a zero is a number and blank space is a character
What is a subquery in SQL?
A subquery is a query inside another query where a query is defined to retrieve data or information back from the database
Subqueries are always executed first and the result of the subquery is passed on to the main query
Can you list the ways to get the count of records in a table?
SELECT * FROM table1;
SELECT COUNT(*) FROM table1;
SELECT rows FROM sysindexes WHERE id =
OBJECT_ID(table1) AND indid < 2
Write a SQL query to find the names of employees that begin with ‘A’?
SELECT * FROM Table_name WHERE EmpName like’A%’
What are group function commands in SQL?
AVG
COUNT
MAX
Min
SUM
VARIANCE
What is the main difference between ‘Between’ and ‘IN’ condition operators?
BETWEEN
- Used to display rows based on a range of values in a row
- SELECT * FROM Students WHERE ROLL_NO BETWEEN 10 and 50
IN
- Used to check for values contained in a specific set of values
- SELECT * FROM students WHERE ROLL_NO IN (8, 15, 25);
What is the difference between the ‘HAVING’ and ‘WHERE’ clauses?
HAVING CLAUSE
- Can be used only with SELECT statement. It is usually used in a GROUP BY clause
WHERE CLAUSE
- WHERE Clause is applied to each row before they are a part of the GROUP BY function in a query
How can you fetch common records from two tables?
You can fetch common records from two tables using INTERSECT
SELECT column1, column2
FROM table_names
WHERE condition
INTERSECT
SELECT column1, column2
FROM table_names
WHERE condition
What are aggregate and scalar functions?
Aggregate Functions
- Used to evaluate mathematical calculation and returns a single value. These calculations are done from the columns in a table
- Ex: MAX(), COUNT()
Scalar Functions
- Scalar functions return single value based on the input value
- Ex: UCASE(), NOW()
