Core Concepts Flashcards
What is SQL?
SQL stands for Structured Query Language. It is a programming language used for storing, retrieving, manipulating and managing structured data in relational databases.
It allows for interacting with databases by writing queries using common commands such as SELECT, JOIN, GROUP BY.
What is a relational database management system?
It is a database system that stores, manages, and organizes data in tables using a structured, relational format.
Data is stored in rows and columns, and relationships between tables are defined using keys (Primary Key, Foreign Key).
Key Features of RDBMS:
✅ Structured Data Storage: Data is stored in tables with predefined schemas.
✅** Uses SQL:** Most RDBMSs use Structured Query Language (SQL) for data operations.
✅ Ensures Data Integrity: Supports constraints like PRIMARY KEY, FOREIGN KEY, UNIQUE, and NOT NULL.
✅ **ACID Compliance: **Ensures Atomicity, Consistency, Isolation, and Durability for reliable transactions.
✅ Scalability: Can handle large amounts of data and support multiple users.
Example: MS SQL Server, MySQL, PostGreSQL etc.
What are the different categories of SQL commands, their abbreviations, and purpose?
- DQL (Data Query Language) - Used to retrieve data from tables.
- DDL (Data Definition Language) Used to change the structure of a database (tables, columns, constraints, etc.).
- DML (Data Manipulation Language) changes the data inside tables.
- DCL (Data Control Language) - Used to manage user permissions and access control.
- TCL (Transaction Control Language) - Used to manage transactions.
What are the all the commands that fall under DQL, DDL, DML, DCL and TCL?
DQL - SELECT
DDL - CREATE, DROP, ALTER, TRUNCATE
DML - INSERT, UPDATE, DELETE
DCL - GRANT, REVOKE
TCL - COMMIT, ROLLBACK, SAVEPOINT
Can you explain the “SELECT” command?
- The SELECT command is used to retrieve data from a table.
- You can retrieve data from specific columns by explicitly mentioning the column names, or all columns in the table using SELECT *.
- Example 1: SELECT name, salary FROM employees
- Example 2: SELECT * FROM employees
What is the “WHERE” command used for?
- It is used to filter results or rows returned by the query based on conditions
- It is used in statements like SELECT, UPDATE and DELETE commands
- Example 1: SELECT * FROM employees WHERE department = ‘HR’
- Example 2: DELETE FROM employees WHERE salary < 30000
- Example 3: UPDATE employees SET salary = 70000 WHERE id = 1
- Always use WHERE in DELETE/UPDATE to avoid accidental full-table changes.
What is the “ORDER BY” used for?
- It is used to sort the results of the query in ascending or descending order, based on one or more columns.
- Multiple columns can be used for sorting. Example: SELECT * FROM employees ORDER BY department ASC, salary DESC
- It works with numeric, text and date columns.
- Use DESC explicitly to sort in descending. Otherwise, it defaults to ascending order.
What is the “DISTINCT” keyword used for?
- It is used to remove duplicate records from the results and return only unique records.
- If it is used on multiple columns, then it filters to the unique combination of all those columns.
- Performance Tip: Using DISTINCT on large datasets can be slow, so consider indexing for optimization.
What is the “TOP” command used for and how does it differ from the “LIMIT” command?
- It is used to retrieve a specific number of rows from the table or result set.
- Differences:
1. TOP is used in SQL Server, whereas LIMIT is used in other database systems such as MySQL.
2. TOP is placed before the “FROM” clause whereas “LIMIT” is placed at the end of the query.
3. TOP command does not support offset, but LIMIT does and is used to skip a specified number of rows in the result set.
4. LIMIT..OFFSET is used in pagination of results.
5. They are used to retrieve the highest and lowest values and for sampling data from a large dataset.
Tip: Use ORDER BY ASC for “bottom” results, ORDER BY DESC for “top” results.
What is “CRUD” in SQL?
- CREATE - INSERT INTO table VALUES (…)
- READ - SELECT * FROM table
- UPDATE - UPDATE table SET column = value WHERE condition
- DELETE - DELETE FROM table WHERE condition
Tip: Always use WHERE in UPDATE and DELETE to avoid modifying or deleting all records by mistake!
What is the MERGE command in SQL, and when would you use it?
“MERGE” is used for Upsert (Update + Insert) operations
- If a record exists (MATCHED), it gets updated.
- If a record does not exist(NOT MATCHED), it gets inserted.
* Optionally, records can be deleted if they no longer meet the conditions(WHEN NOT MATCHED BY SOURCE THEN DELETE).
It is used to synchronize data into two tables in SQL Server.
Syntax:
MERGE INTO target_table AS tgt –Specifies table where changes will be applied.
USING source_table AS src –Defines the source of new or updated data.
ON tgt.id = src.id –Defines the condition for matching records.
WHEN MATCHED THEN –Updates the record if a match is found.
UPDATE SET tgt.name = src.name, tgt.salary = src.salary
WHEN NOT MATCHED THEN –Inserts a new record if no match is found.
INSERT (id, name, salary) VALUES (src.id, src.name, src.salary);
Example:
MERGE INTO employees AS tgt
USING new_employees AS src
ON tgt.emp_id = src.emp_id
WHEN MATCHED THEN
UPDATE SET tgt.salary = src.salary
WHEN NOT MATCHED THEN
INSERT (emp_id, name, salary) VALUES (src.emp_id, src.name, src.salary)
WHEN NOT MATCHED BY SOURCE THEN
DELETE; – Removes records that are in target but not in source
What is the difference between CREATE and ALTER in SQL?
Both CREATE and ALTER are DDL (Data Definition Language) commands used to define and modify the structure of database and objects - tables, views, indexes, stored procedures.
The CREATE command is used to create new database objects, whereas the ALTER command is used to modify an existing database object.
Possibilities with ALTER:
* Adding, modifying, or deleting columns from a table.
* Renaming tables.
* Changing data types or constraints.
How do you add a new column to an existing table?
ALTER TABLE table_name ADD COLUMN column_name type
How do you change a column’s data type in SQL?
ALTER TABLE table_name ALTER COLUMN column_name data_type
Can you remove a column from a table without dropping the table?
ALTER TABLE table_name DROP COLUMN column_name
How would you rename a table in SQL?
ALTER TABLE table_name RENAME TO new_table_name
What happens when you use DROP TABLE in SQL?
The entire table gets deleted along with the data in the table. This operation cannot be rolledback.
How is DROP different from TRUNCATE and DELETE?
- Use DROP if you never need the object again.
- Use DELETE to remove specific rows.
- Use TRUNCATE to remove all rows but keep the table structure.
What Happens If You Try to Drop a Table with a Foreign Key Constraint?
If you attempt to DROP a table that has a foreign key constraint, the database will prevent you from dropping it unless you explicitly handle the constraint.
This is because the foreign key ensures referential integrity, meaning that if another table depends on this table, deleting it could cause orphaned records.
Can You Rollback a DROP Operation?
In most databases, DROP is an auto-committed operation and cannot be rolled back.
However, in databases that support transactional DDL (like PostgreSQL and SQL Server), if DROP is inside an active transaction (BEGIN TRANSACTION), it can be rolled back before committing.
Otherwise, recovery options depend on backups and snapshots.
How to “RENAME” tables, columns, indexes, constraints etc. in MS SQL Server?
- In SQL Server, you CANNOT use ALTER TABLE RENAME TO like in PostgreSQL or MySQL. Instead, you must use sp_rename.
- SQL Server uses sp_rename as a system procedure instead of ALTER TABLE RENAME.
- This provides more flexibility, allowing renaming of indexes, constraints, and other objects, not just tables and columns.
Renaming a table:
EXEC sp_rename ‘old_table_name’, ‘new_table_name’;
What are SQL constraints? What are they used for? An, what are the different types of constraints?
SQL constraints are rules applied to tables or columns to enforce data integrity and consistency.
The different types of constraints are:
* Primary Key - Ensures each row in the table has a unique identifier. It automatically enforces UNIQUE and NOT NULL constraints.
* Foreign Key - Creates relationship between tables to enforce referential integrity. It prevents orphaned records.
* Unique Key - Prevents duplicate values in a column
* Not Null - Ensures a column always has a value to prevent missing or incomplete data.
* Check - Restricts values in a column using a range or condition, to prevent invalid data entries
* Default - Automatically assigns a value if none is provided. Prevents NULL values when a user forgets to insert a value.
