SQL Tutorials 10 hours

Certainly! Here's a tutorial in the form of a flash card table for MySQL SQL commands:

SQL Command & Explanation	SQL Example
SELECT - Retrieves data from one or more tables.	SELECT first_name, last_name FROM employees;
FROM - Specifies the table to retrieve data from.	SELECT first_name FROM employees;
WHERE - Filters records based on a condition.	SELECT * FROM employees WHERE age > 30;
AND, OR - Combine conditions in a WHERE clause.	SELECT * FROM employees WHERE age > 30 AND department = 'HR';
ORDER BY - Sorts the result set.	SELECT * FROM employees ORDER BY last_name ASC;
INSERT INTO - Adds new records into a table.	INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
UPDATE - Modifies existing records in a table.	UPDATE employees SET age = 31 WHERE first_name = 'John' AND last_name = 'Doe';
DELETE - Removes records from a table.	DELETE FROM employees WHERE last_name = 'Doe';
CREATE DATABASE - Creates a new database.	CREATE DATABASE new_database;
DROP DATABASE - Deletes an existing database.	DROP DATABASE old_database;
CREATE TABLE - Creates a new table.	CREATE TABLE employees (id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50));
DROP TABLE - Deletes an existing table.	DROP TABLE old_table;
ALTER TABLE - Modifies an existing table structure.	ALTER TABLE employees ADD COLUMN age INT;
DISTINCT - Removes duplicate rows from the result set.	SELECT DISTINCT department FROM employees;
COUNT - Counts the number of rows in a result set.	SELECT COUNT(*) FROM employees;
GROUP BY - Groups rows that have the same values.	SELECT department, COUNT(*) FROM employees GROUP BY department;
HAVING - Filters the results of a GROUP BY.	SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;
INNER JOIN - Combines rows from two tables based on a related column.	SELECT employees.first_name, departments.name FROM employees INNER JOIN departments ON employees.department_id = departments.id;
LEFT JOIN - Combines rows from the main table and the matching rows from a secondary table.	SELECT employees.first_name, departments.name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;
RIGHT JOIN - Opposite of LEFT JOIN. Combines rows from the secondary table and the matching rows from the main table.	SELECT employees.first_name, departments.name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;
UNION - Combines the result of two or more SELECT statements (removes duplicates).	SELECT first_name FROM employees UNION SELECT first_name FROM managers;
LIMIT - Limits the number of rows in the result set.	SELECT * FROM employees LIMIT 10;

This table is a brief summary and focuses on some of the most commonly used SQL commands. SQL is a very rich language, and there are many advanced features, functions, and commands not covered in this flash card format. If you're looking to learn SQL in depth, it would be beneficial to explore each of these commands in detail, practice with real-world scenarios, and also dive into other advanced topics.

SQL Command & Explanation	SQL Example
BETWEEN - Filters records within a specific range.	SELECT * FROM employees WHERE salary BETWEEN 50000 AND 60000;
LIKE - Searches for a specified pattern in a column.	SELECT * FROM employees WHERE first_name LIKE 'Jo%';
IN - Filters records based on a list of values.	SELECT * FROM employees WHERE department IN ('HR', 'Finance');
NOT - Displays records where the condition is NOT TRUE.	SELECT * FROM employees WHERE NOT age = 30;
IS NULL - Filters records with NULL values.	SELECT * FROM employees WHERE address IS NULL;
IS NOT NULL - Filters records without NULL values.	SELECT * FROM employees WHERE address IS NOT NULL;
AS - Renames a column or table with an alias.	SELECT first_name AS FirstName, last_name AS LastName FROM employees;
UNION ALL - Combines the result of two or more SELECT statements (does not remove duplicates).	SELECT first_name FROM employees UNION ALL SELECT first_name FROM managers;
ANY - Compares a value to any value in another result set.	SELECT * FROM products WHERE price > ANY (SELECT price FROM discounted_products);
ALL - Compares a value to all values in another result set.	SELECT * FROM products WHERE price > ALL (SELECT price FROM discounted_products);
EXISTS - Tests for the existence of any record in a subquery.	SELECT first_name FROM employees WHERE EXISTS (SELECT department FROM departments WHERE name = 'HR');
CASE - Sets a condition in a query.	SELECT first_name, CASE WHEN age < 30 THEN 'Young' ELSE 'Old' END AS age_group FROM employees;
TRUNCATE - Removes all records from a table (without deleting the table itself).	TRUNCATE TABLE temporary_data;
INDEX - Creates or deletes an index in a table.	CREATE INDEX idx_lastname ON employees (last_name);
AUTO_INCREMENT - Automatically increases the value of a field in a table.	CREATE TABLE persons (ID int NOT NULL AUTO_INCREMENT, name varchar(255), PRIMARY KEY (ID));
DATE - Extracts the date part of a date or date/time expression.	SELECT DATE(datetime_column) FROM table_name;
AVG - Returns the average value of a numeric column.	SELECT AVG(salary) FROM employees;
SUM - Returns the total sum of a numeric column.	SELECT SUM(salary) FROM employees;

SQL Command & Explanation	SQL Example
MIN - Returns the smallest value in a numeric column.	SELECT MIN(salary) FROM employees;
MAX - Returns the largest value in a numeric column.	SELECT MAX(salary) FROM employees;
ROUND - Rounds a number to the specified number of decimals.	SELECT ROUND(salary, 2) FROM employees;
CHAR_LENGTH - Returns the character length of a string.	SELECT first_name, CHAR_LENGTH(first_name) AS length FROM employees;
UPPER - Converts a string to upper-case.	SELECT UPPER(first_name) FROM employees;
LOWER - Converts a string to lower-case.	SELECT LOWER(first_name) FROM employees;
NOW - Returns the current date and time.	SELECT NOW();
DATE_FORMAT - Formats a date as specified.	SELECT DATE_FORMAT(birthdate, '%M %d %Y') AS formatted_date FROM employees;
CONCAT - Concatenates two or more strings.	SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
COALESCE - Returns the first non-null value in a list.	SELECT COALESCE(NULL, 'default', 'value') AS result;
CAST - Converts one data type to another.	SELECT CAST(age AS CHAR) AS age_string FROM employees;
FOREIGN KEY - Ensures referential integrity in the relationship between two tables.	ALTER TABLE orders ADD FOREIGN KEY (employee_id) REFERENCES employees(id);
PRIMARY KEY - Uniquely identifies each record in a table.	CREATE TABLE employees (id INT PRIMARY KEY, name VARCHAR(255));
CHECK - Ensures that the values in a column meet a specific condition.	CREATE TABLE employees (id INT PRIMARY KEY, age INT CHECK(age >= 18));
DEFAULT - Sets a default value for a column.	CREATE TABLE employees (id INT PRIMARY KEY, country VARCHAR(255) DEFAULT 'USA');
INDEX - Used to create and retrieve data from the database quickly.	CREATE INDEX idx_lastname ON employees (last_name);
VIEW - A virtual table based on the result-set of an SQL statement.	CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

SQL Command & Explanation	SQL Example
SUBSTRING - Extracts a part of a string.	SELECT SUBSTRING(first_name, 1, 3) AS short_name FROM employees;
REPLACE - Replaces all occurrences of a substring.	SELECT REPLACE(email, '@company.com', '@newdomain.com') FROM employees;
LEFT - Extracts a number of characters from a string (starting from left).	SELECT LEFT(first_name, 3) FROM employees;
RIGHT - Extracts a number of characters from a string (starting from right).	SELECT RIGHT(first_name, 2) FROM employees;
LTRIM and RTRIM - Removes leading or trailing spaces.	SELECT LTRIM(column_name), RTRIM(column_name) FROM table_name;
CURDATE - Returns the current date.	SELECT CURDATE();
CURTIME - Returns the current time.	SELECT CURTIME();
DATEDIFF - Returns the difference between two dates.	SELECT DATEDIFF('2023-12-31', CURDATE()) AS days_until_end_of_year;
ABS - Returns the absolute value.	SELECT ABS(-5) AS absolute_value;
MOD - Returns the remainder of a division.	SELECT MOD(10, 3) AS remainder;
CEIL or CEILING - Rounds a number up to the nearest whole number.	SELECT CEIL(7.8) AS rounded_up;
FLOOR - Rounds a number down to the nearest whole number.	SELECT FLOOR(7.8) AS rounded_down;
RAND - Generates a random number.	SELECT RAND();
LENGTH - Returns the length of a string in bytes.	SELECT LENGTH(first_name) FROM employees;
POSITION - Returns the position of a substring in a string.	SELECT POSITION('world' IN 'Hello world') AS position;
TRIM - Removes specified prefixes or suffixes from a string.	SELECT TRIM(' ' FROM column_name) FROM table_name;
DAY, MONTH, YEAR - Extracts the day, month, or year from a date.	SELECT DAY(birthdate), MONTH(birthdate), YEAR(birthdate) FROM employees;
CONCAT_WS - Concatenates multiple strings with a separator.	SELECT CONCAT_WS('-', year, month, day) AS formatted_date FROM date_table;
LAST_INSERT_ID - Returns the last AUTO_INCREMENT id used.	INSERT INTO table_name (name) VALUES ('John'); SELECT LAST_INSERT_ID();

SQL Command & Explanation	SQL Example
STR_TO_DATE - Converts a string into a date.	SELECT STR_TO_DATE('2023,10,24','%Y,%m,%d');
DAYNAME - Returns the name of the weekday.	SELECT DAYNAME(CURDATE()) AS weekday_name;
DAYOFWEEK - Returns the weekday index of a date (1=Sunday, 7=Saturday).	SELECT DAYOFWEEK(CURDATE()) AS weekday_index;
EXTRACT - Extracts parts from a date.	SELECT EXTRACT(YEAR FROM '2023-10-24') AS year;
TIME_FORMAT - Formats the time.	SELECT TIME_FORMAT('14:50:00', '%h:%i %p') AS formatted_time;
CASE - Sets conditions within queries.	SELECT CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_grade FROM employees;
IFNULL - Returns an alternative value if the expression is NULL.	SELECT IFNULL(address, 'No Address') FROM employees;
COALESCE - Returns the first non-NULL value in a list.	SELECT COALESCE(first_name, last_name, 'Guest') FROM users;
CHAR - Converts an ASCII value to a character.	SELECT CHAR(65); (Result: 'A')
ASCII - Returns the ASCII value of a character.	SELECT ASCII('A'); (Result: 65)
REVERSE - Reverses a string.	SELECT REVERSE('SQL'); (Result: 'LQS')
STARTS WITH - Checks if a string starts with a specified prefix.	SELECT name FROM users WHERE name LIKE 'Jo%';
ENDS WITH - Checks if a string ends with a specified suffix.	SELECT name FROM users WHERE name LIKE '%son';
LOCATE - Finds the position of the first occurrence of a substring.	SELECT LOCATE('or', 'Hello World'); (Result: 8)
GET_LOCK - Tries to get a lock with a given name.	SELECT GET_LOCK('lock_name', 10);
RELEASE_LOCK - Releases the lock with the given name.	SELECT RELEASE_LOCK('lock_name');

 

SQL Full Course In 10 Hours

Basic SQL Queries

SQL Function Name Example Query SELECT SELECT first_name, last_name FROM employees; INSERT INTO INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe'); UPDATE UPDATE employees SET last_name = 'Smith' WHERE employee_id = 1; DELETE DELETE FROM employees WHERE employee_id = 1; CREATE TABLE CREATE TABLE employees (employee_id INT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100)); ALTER TABLE ALTER TABLE employees ADD COLUMN email VARCHAR(255); DROP TABLE DROP TABLE employees; JOIN SELECT e.first_name, e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; GROUP BY SELECT department_id, COUNT(*) FROM employees GROUP BY department_id; ORDER BY SELECT first_name, last_name FROM employees ORDER BY first_name; LIKE SELECT * FROM employees WHERE first_name LIKE 'J%'; IN SELECT * FROM employees WHERE department_id IN (1, 2, 3); BETWEEN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 75000; IS NULL SELECT * FROM employees WHERE manager_id IS NULL; COUNT() SELECT COUNT(*) FROM employees; SUM() SELECT SUM(salary) FROM employees; AVG() SELECT AVG(salary) FROM employees; MIN() SELECT MIN(salary) FROM employees; MAX() SELECT MAX(salary) FROM employees; Normalization in SQL

Normalization Form Description and Example First Normal Form (1NF) Description: Ensures that each column contains unique values and each column's value is atomic. Example: A column cannot hold multiple values like a list; instead, each value must be in a separate row. Second Normal Form (2NF) Description: Ensures that all non-key attributes are fully functional dependent on the primary key. Example: If a table contains columns for employee ID, employee name, and department, where department is dependent only on employee ID, the table is in 2NF. Third Normal Form (3NF) Description: Ensures that all the columns in a table are only dependent on the primary key. Example: If an address column depends on an employee column which is not a primary key, the table must be split to move the non-dependent column to another table where it is dependent on a primary key. Boyce-Codd Normal Form (BCNF) Description: A stricter version of 3NF where every determinant must be a candidate key. Example: If a table has composite primary key (employee ID, department ID) and has a column department location which depends only on department ID, it must be split to conform to BCNF.

Triggers in SQL

SQL Function Name Example SQL CREATE TRIGGER CREATE TRIGGER example_trigger BEFORE INSERT ON employees FOR EACH ROW BEGIN UPDATE salaries SET total = total + NEW.salary; END; BEFORE INSERT CREATE TRIGGER before_insert_example BEFORE INSERT ON orders FOR EACH ROW SET NEW.order_date = CURRENT_DATE(); AFTER INSERT CREATE TRIGGER after_insert_example AFTER INSERT ON orders FOR EACH ROW BEGIN INSERT INTO log_orders VALUES (NEW.order_id, NEW.customer_id, 'inserted'); END; BEFORE UPDATE CREATE TRIGGER before_update_example BEFORE UPDATE ON employees FOR EACH ROW BEGIN SET NEW.last_updated = NOW(); END; AFTER UPDATE CREATE TRIGGER after_update_example AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO change_log VALUES (NEW.emp_id, 'updated'); END; BEFORE DELETE CREATE TRIGGER before_delete_example BEFORE DELETE ON employees FOR EACH ROW BEGIN INSERT INTO archive_employees SELECT * FROM deleted; END; AFTER DELETE CREATE TRIGGER after_delete_example AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO log_deletes VALUES (OLD.emp_id, OLD.name, 'deleted'); END;

Based on the document "SQL Full Course In 10 Hours.txt", here's a summary of the advantages and disadvantages of using triggers in SQL: ### Advantages of Triggers 1. **Forced Security Protocols**: Triggers help enforce security protocols on the tables within a database. 2. **Data Integrity**: They ensure the integrity of the data within the database by performing checks or actions automatically. 3. **Automated Responses**: Triggers can automatically respond to changes within a database, ensuring that all necessary updates and checks are performed immediately. 4. **Error Handling**: They can handle errors from the database layer, potentially reducing the amount of erroneous data. 5. **Data Change Insights**: Triggers can be used to monitor and log changes in the data, which is useful for auditing purposes. ### Disadvantages of Triggers 1. **Limited Validation Capabilities**: While triggers are great for extended validation, they cannot replace all types of data validation constraints such as NOT NULL, UNIQUE, CHECK, and FOREIGN KEY. 2. **Performance Overhead**: Triggers can increase the overhead on a database system because they are additional processes that must be executed. 3. **Complexity in Troubleshooting**: Since triggers execute automatically and can be invisible to client applications, they can make troubleshooting database issues more complex. These points give a balanced view of how triggers function within SQL databases, helping users make informed decisions about when and how to use them effectively.

Joins in SQL

SQL Function Name Example SQL INNER JOIN SELECT e.employee_id, e.first_name, p.project_id FROM employees e INNER JOIN projects p ON e.employee_id = p.project_id; LEFT JOIN SELECT e.employee_id, e.first_name, p.project_id FROM employees e LEFT JOIN projects p ON e.employee_id = p.project_id; RIGHT JOIN SELECT e.employee_id, e.first_name, p.project_id FROM employees e RIGHT JOIN projects p ON e.employee_id = p.project_id; FULL OUTER JOIN SELECT e.employee_id, e.first_name, p.project_id FROM employees e FULL OUTER JOIN projects p ON e.employee_id = p.project_id; CROSS JOIN SELECT e.employee_id, p.project_id FROM employees e CROSS JOIN projects p; SELF JOIN SELECT a.employee_id, b.employee_id FROM employees a, employees b WHERE a.manager_id = b.employee_id; NATURAL JOIN SELECT * FROM employees NATURAL JOIN projects;

Functions in SQL

SQL Function Name Example SQL COUNT() SELECT COUNT(*) FROM employees; SUM() SELECT SUM(salary) FROM employees; AVG() SELECT AVG(salary) FROM employees; MIN() SELECT MIN(salary) FROM employees; MAX() SELECT MAX(salary) FROM employees; UPPER() SELECT UPPER(first_name) FROM employees; LOWER() SELECT LOWER(first_name) FROM employees; LENGTH() SELECT LENGTH(first_name) FROM employees; CONCAT() SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees; SUBSTRING() SELECT SUBSTRING(first_name, 1, 3) FROM employees; REPLACE() SELECT REPLACE(email, 'example.com', 'test.com') FROM employees; NOW() SELECT NOW(); COALESCE() SELECT COALESCE(address, 'Unknown') FROM employees; ROUND() SELECT ROUND(salary, 0) FROM employees; DATE_FORMAT() SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_date FROM employees;

Stored procedure

SQL Function Name Example SQL CREATE PROCEDURE CREATE PROCEDURE GetEmployeeCount() BEGIN SELECT COUNT(*) FROM Employees; END; CALL PROCEDURE CALL GetEmployeeCount(); UPDATE PROCEDURE CREATE PROCEDURE UpdateEmployeeSalary(IN empID INT, IN newSalary DECIMAL(10,2)) BEGIN UPDATE Employees SET salary = newSalary WHERE employee_id = empID; END; DELETE PROCEDURE CREATE PROCEDURE DeleteEmployee(IN empID INT) BEGIN DELETE FROM Employees WHERE employee_id = empID; END; INSERT PROCEDURE CREATE PROCEDURE InsertEmployee(IN empFirstName VARCHAR(255), IN empLastName VARCHAR(255), IN empSalary DECIMAL(10,2)) BEGIN INSERT INTO Employees (first_name, last_name, salary) VALUES (empFirstName, empLastName, empSalary); END;

User-Defined Functions

SQL Function Name Example SQL CREATE FUNCTION CREATE FUNCTION TotalOrders (@CustomerID int) RETURNS int AS BEGIN RETURN (SELECT COUNT(*) FROM Orders WHERE CustomerID = @CustomerID); END; DROP FUNCTION DROP FUNCTION TotalOrders; ALTER FUNCTION ALTER FUNCTION TotalOrders (@CustomerID int) RETURNS int AS BEGIN RETURN (SELECT COUNT(*) FROM Orders WHERE CustomerID = @CustomerID AND OrderDate > '2020-01-01'); END; EXECUTE FUNCTION SELECT dbo.TotalOrders(1);

SQL Interview Question & Answers

SQL Interview Question Name Example SQL Answer What is SQL? SELECT version(); How to create a database? CREATE DATABASE exampleDB; How to delete a database? DROP DATABASE exampleDB; What are SQL joins? SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id; What is a primary key? ALTER TABLE table1 ADD PRIMARY KEY (id); How to use the WHERE clause? SELECT * FROM table1 WHERE name = 'John Doe'; What is the difference between DELETE and TRUNCATE? DELETE FROM table1 WHERE id = 1; and TRUNCATE TABLE table1; How to use subqueries? SELECT * FROM table1 WHERE id IN (SELECT id FROM table2); How to implement pagination in SQL? SELECT * FROM table1 LIMIT 10 OFFSET 20; What is normalization? SELECT * FROM table1 JOIN table2 ON table1.id = table2.ref_id WHERE table1.status = 'active';

SQL For Data Science

### Introduction to SQL for Data Science - **Definition**: Explain what SQL (Structured Query Language) is and its role in data science for handling and analyzing data stored in relational databases. - **Importance**: Discuss the significance of SQL in data science, emphasizing its utility in data manipulation, querying, and reporting. ### Basic SQL Concepts - **Databases and Tables**: Introduction to databases and how data is organized in tables. - **Data Types**: Overview of common SQL data types and their applications in data science. - **SQL Syntax**: Basic syntax for writing SQL queries, focusing on readability and structure. ### SQL Operations - **Creating Databases and Tables**: Simple SQL commands to create databases and tables, tailored for data science projects. - **CRUD Operations**: Cover the essentials of Create, Read, Update, and Delete operations in SQL.    ### Data Retrieval Techniques - **SELECT Statement**: How to retrieve data using SELECT, focusing on selecting specific columns and filtering rows with WHERE clauses. - **Aggregation Functions**: Introduction to functions like COUNT, SUM, AVG, MIN, and MAX for basic data analysis. - **Sorting and Filtering**: Using ORDER BY and WHERE to sort and filter datasets, crucial for data exploration. ### Advanced Data Manipulation - **JOIN Operations**: Explanation of INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN with practical examples relevant to data science. - **Subqueries and Aliases**: How to use subqueries and aliases to simplify complex queries and enhance readability. - **Grouping Data**: Using GROUP BY and HAVING clauses to aggregate data, which is essential for statistical analysis in data science. ### SQL Functions - **Scalar and Aggregate Functions**: Detailed examples of both scalar functions (like UPPER, LOWER) and aggregate functions (like COUNT, AVG). - **Date and Time Functions**: How to handle date and time data, which is common in time-series analysis. ### Optimization Techniques - **Indexes**: Introduction to indexes and how they improve query performance, important for large datasets in data science. - **Views**: Using views to simplify access to frequently used and complex queries. ### Integrating SQL with Data Science Tools - **SQL and Python**: Demonstrate how SQL can be integrated with Python using libraries like pandas and sqlalchemy for data analysis. - **SQL in BI Tools**: Brief overview of using SQL within popular Business Intelligence tools like Tableau or Power BI for data visualization. ### Practice Problems - **Example Queries**: Provide practical SQL queries based on typical data science scenarios. - **Exercises and Solutions**: Include exercises that cover each topic with solutions to reinforce learning. ### Conclusion - **Summary**: Recap the key points covered in the tutorial, emphasizing the role of SQL in data analysis and data science. - **Further Resources**: Suggest books, online courses, and practice platforms to continue learning SQL for more advanced data science applications. This structure aims to make SQL concepts accessible and memorable for beginners, especially those interested in applying SQL within the field of data science.

SQL Command Categories

SQL Command Categories Examples of SQLs Data Definition Language (DDL) CREATE TABLE Employees (ID INT, Name VARCHAR(255)); ALTER TABLE Employees ADD Email VARCHAR(255); DROP TABLE Employees; Data Manipulation Language (DML) INSERT INTO Employees (ID, Name) VALUES (1, 'John Doe'); UPDATE Employees SET Name = 'Jane Doe' WHERE ID = 1; DELETE FROM Employees WHERE ID = 1; Data Control Language (DCL) GRANT SELECT ON Employees TO User1; REVOKE SELECT ON Employees FROM User1; Transaction Control Language (TCL) BEGIN TRANSACTION; COMMIT; ROLLBACK; Data Query Language (DQL) SELECT * FROM Employees; SELECT Name FROM Employees WHERE ID = 1;

ER Diagram

Keys in Database

Keys in Database SQL Examples Primary Key sql CREATE TABLE Students ( StudentID int PRIMARY KEY, FirstName varchar(255), LastName varchar(255) ); sql ALTER TABLE Students ADD PRIMARY KEY (StudentID); Foreign Key sql CREATE TABLE Orders ( OrderID int PRIMARY KEY, StudentID int, FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ); sql ALTER TABLE Orders ADD FOREIGN KEY (StudentID) REFERENCES Students(StudentID); Unique Key sql CREATE TABLE Employees ( EmployeeID int PRIMARY KEY, Email varchar(255) UNIQUE ); sql ALTER TABLE Employees ADD CONSTRAINT UC_Email UNIQUE (Email); Composite Key sql CREATE TABLE Enrollments ( StudentID int, CourseID int, PRIMARY KEY (StudentID, CourseID) ); Candidate Key sql CREATE TABLE Products ( ProductID int PRIMARY KEY, ProductCode varchar(255) UNIQUE, ProductName varchar(255) ); Alternate Key sql CREATE TABLE Cars ( CarID int PRIMARY KEY, LicensePlate varchar(255) UNIQUE ); In this case, LicensePlate is an alternate key. Super Key sql CREATE TABLE Books ( ISBN int PRIMARY KEY, Title varchar(255), Author varchar(255) ); ISBN, Title, Author combined is a super key. Composite Primary Key sql CREATE TABLE ExamResults ( StudentID int, ExamID int, Score int, PRIMARY KEY (StudentID, ExamID) ); Surrogate Key sql CREATE TABLE Users ( UserID int IDENTITY(1,1) PRIMARY KEY, UserName varchar(255) );

Keys in Database Explanation Primary Key A unique identifier for each record in a table. Ensures that no two rows have the same value. It's used when you need to uniquely identify each record in a table. Foreign Key A key used to link two tables together. It is a field in one table that is a primary key in another table. Use it to establish relationships between tables, ensuring data consistency. Unique Key Ensures all values in a column are unique. Similar to the primary key, but it can accept one null value. Use it when you need to ensure data uniqueness in a column that is not a primary key. Composite Key A primary key made up of two or more columns to uniquely identify each record in a table. Use it when a single column is not sufficient to uniquely identify records. Candidate Key An attribute or a set of attributes that can uniquely identify a record in a table. Any candidate key can become a primary key. Used during the design of a database to determine which key will be the primary key. Alternate Key A candidate key that is not chosen as the primary key. Use it as an alternative to the primary key if needed. Super Key A set of one or more keys that can uniquely identify a record in a table. It is a superset of the primary key. Use it to define any key that can uniquely identify records in a table. Surrogate Key An artificial key generated by the database. It has no business meaning and is usually an auto-incremented value. Use it when there is no natural primary key available in the data.

Constraints in Database

Constraints in Database Examples NOT NULL CREATE TABLE Students ( StudentID int NOT NULL, FirstName varchar(255) NOT NULL, Age int ); ALTER TABLE Students MODIFY Age int NOT NULL; UNIQUE CREATE TABLE Employees ( EmployeeID int PRIMARY KEY, Email varchar(255) UNIQUE ); ALTER TABLE Employees ADD CONSTRAINT UC_Email UNIQUE (Email); PRIMARY KEY CREATE TABLE Departments ( DepartmentID int PRIMARY KEY, DepartmentName varchar(255) ); ALTER TABLE Departments ADD PRIMARY KEY (DepartmentID); FOREIGN KEY CREATE TABLE Orders ( OrderID int PRIMARY KEY, CustomerID int, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); ALTER TABLE Orders ADD CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID); CHECK CREATE TABLE Products ( ProductID int PRIMARY KEY, Price decimal(10, 2), CHECK (Price > 0) ); ALTER TABLE Products ADD CHECK (Price >= 0); DEFAULT CREATE TABLE Orders ( OrderID int PRIMARY KEY, OrderDate datetime DEFAULT GETDATE() ); ALTER TABLE Orders ADD CONSTRAINT DF_OrderDate DEFAULT GETDATE() FOR OrderDate; INDEX CREATE INDEX idx_lastname ON Employees (LastName); CREATE UNIQUE INDEX idx_email ON Employees (Email);

Constraints in Database Explanation NOT NULL Ensures that a column cannot have a NULL value. Use it when you need to ensure that a field must always have data. For example, a username column should never be empty. UNIQUE Ensures that all values in a column are different. Use it when you need to prevent duplicate values in a column, such as an email field in a user table. PRIMARY KEY A combination of NOT NULL and UNIQUE. Uniquely identifies each row in a table. Use it when you need to define a unique identifier for each record, such as an ID column. FOREIGN KEY Ensures referential integrity by linking a column in one table to the primary key in another table. Use it to establish relationships between tables, ensuring data consistency. CHECK Ensures that the values in a column satisfy a specific condition. Use it when you need to enforce rules at the column level, like a salary column that must be greater than zero. DEFAULT Provides a default value for a column when none is specified. Use it when you want to automatically insert a specific value into a column if no value is provided, such as setting a status column to 'active' by default. INDEX Improves the speed of data retrieval. Use it when you need to enhance the performance of queries that use specific columns frequently, such as searching by last_name.

Operators in SQL

Function Name Example of SQL Arithmetic Operators SELECT 10 + 5; SELECT 10 - 5; SELECT 10 * 5; SELECT 10 / 5; Comparison Operators SELECT * FROM Employees WHERE Age = 30; SELECT * FROM Employees WHERE Salary > 50000; SELECT * FROM Employees WHERE Salary BETWEEN 30000 AND 60000; Logical Operators SELECT * FROM Employees WHERE Age > 30 AND Salary > 50000; SELECT * FROM Employees WHERE Age < 25 OR Department = 'HR'; SELECT * FROM Employees WHERE NOT (Age < 18); Bitwise Operators SELECT 5 & 3; SELECT 5 LIKE Operator SELECT * FROM Customers WHERE Name LIKE 'A%'; SELECT * FROM Customers WHERE Name LIKE '%son'; SELECT * FROM Customers WHERE Name LIKE '_r%'; IN Operator SELECT * FROM Employees WHERE Department IN ('HR', 'Sales'); SELECT * FROM Products WHERE CategoryID IN (1, 2, 3); BETWEEN Operator SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'; SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 60000; IS NULL / IS NOT NULL SELECT * FROM Employees WHERE Department IS NULL; SELECT * FROM Employees WHERE Department IS NOT NULL; AND / OR Operators SELECT * FROM Employees WHERE Age > 30 AND Department = 'Sales'; SELECT * FROM Employees WHERE Age < 25 OR Department = 'HR'; NOT Operator SELECT * FROM Employees WHERE NOT Age < 18; SELECT * FROM Orders WHERE NOT Status = 'Shipped'; EXISTS Operator SELECT * FROM Employees WHERE EXISTS (SELECT * FROM Orders WHERE Employees.EmployeeID = Orders.EmployeeID);

Views in SQL

Function Name Example of SQL CREATE VIEW CREATE VIEW StudentView AS SELECT StudentID, FirstName, LastName FROM Students; CREATE VIEW HighSalaryEmployees AS SELECT EmployeeID, FirstName, Salary FROM Employees WHERE Salary > 50000; SELECT FROM VIEW SELECT * FROM StudentView; SELECT * FROM HighSalaryEmployees; UPDATE VIEW CREATE OR REPLACE VIEW EmployeeView AS SELECT EmployeeID, FirstName, Salary FROM Employees WHERE Salary > 60000; UPDATE EmployeeView SET Salary = Salary + 5000 WHERE EmployeeID = 101; DROP VIEW DROP VIEW IF EXISTS StudentView; DROP VIEW HighSalaryEmployees; Complex View CREATE VIEW OrderSummary AS SELECT Customers.CustomerID, Customers.CustomerName, SUM(Orders.TotalAmount) AS TotalAmount FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID, Customers.CustomerName;

Exception handling in SQL

Function Name Example of SQL TRY...CATCH Block BEGIN TRY INSERT INTO Employees (ID, Name) VALUES (1, 'John'); END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; BEGIN TRY UPDATE Orders SET OrderDate = '2024-01-01' WHERE OrderID = 100; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH; RAISERROR BEGIN TRY IF @Amount < 0 RAISERROR ('Amount cannot be negative', 16, 1); END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; RAISERROR ('Custom error message', 16, 1); THROW BEGIN TRY IF @Value IS NULL THROW 50000, 'Value cannot be null', 1; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; THROW 51000, 'An unexpected error occurred', 1; ERROR_MESSAGE() BEGIN TRY DELETE FROM Orders WHERE OrderID = 10; END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH; ERROR_NUMBER() BEGIN TRY INSERT INTO Customers (CustomerID, Name) VALUES (1, 'Alice'); END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage; END CATCH; TRANSACTION with TRY...CATCH BEGIN TRY BEGIN TRANSACTION DELETE FROM Orders WHERE OrderID = 10; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH;

SQL Server Interview Question & Answer

No. SQL Interview Questions Example of Answers 1 What is the difference between DELETE and TRUNCATE statements? DELETE is used to delete rows from a table and is DML. TRUNCATE deletes all rows and is DDL and cannot be rolled back. 2 What are the different subsets of SQL? DDL, DML, DCL, and TCL. DDL for schema definition, DML for data manipulation, DCL for rights, and TCL for transaction control. 3 What do you mean by Database Management Systems (DBMS)? DBMS is a software application that interacts with users, apps, and the database itself to capture and analyze data. Types include hierarchical, relational, network, and object-oriented. 4 What is a Table and a Field in SQL? A table is a collection of data in rows and columns. A field is a column in a table. Example: An employee table with fields like employee ID and name. 5 What are JOINS in SQL? Joins are used to combine rows from two or more tables based on a related column. Types include INNER JOIN, FULL JOIN, LEFT JOIN, and RIGHT JOIN. 6 What is the difference between CHAR and VARCHAR2 data types in SQL? CHAR is for fixed-length strings; VARCHAR2 is for variable-length strings. VARCHAR2 is more flexible and space-efficient for strings of varying lengths. 7 What is a PRIMARY KEY? A PRIMARY KEY uniquely identifies each row in a table. It must contain unique values and cannot contain NULLs. Example: Employee ID in an employee table. 8 What are CONSTRAINTS? Constraints enforce limits on the data type of a table, such as NOT NULL, UNIQUE, CHECK, DEFAULT, and INDEX. They ensure the accuracy and reliability of the data in the database. 9 What is the difference between SQL and MySQL? SQL is a query language for managing databases; MySQL is a database management system that uses SQL to manage the data it stores. 10 What is a UNIQUE KEY? A UNIQUE KEY ensures that all values in a column are different. It allows one NULL value and ensures no duplicate values are present. 11 What is a FOREIGN KEY? A FOREIGN KEY is a key used to link two tables together. It is a field (or collection of fields) in one table that uniquely identifies a row of another table. 12 What do you mean by data integrity? Data integrity refers to the accuracy and consistency of data. It is maintained through a set of constraints, rules, and procedures to ensure the data is accessible and accurate. 13 What is the difference between clustered and non-clustered indexes? Clustered indexes sort and store the data rows in the table based on their key values. Non-clustered indexes store a separate structure from the data rows, which can be constructed from one or more columns of the table. 14 Write a SQL query to display the current date. SELECT GETDATE(); This SQL command returns the current date and time in SQL Server. 15 What are the different types of JOINs? The different types include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. INNER JOIN returns rows when there is a match in both tables, and LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. 16 What do you mean by denormalization? Denormalization is the process of trying to improve the read performance of a database by adding redundant data or by grouping data. 17 What are entities and relationships in SQL? Entities are objects or concepts that can have data stored about them. Relationships represent how two or more entities are related to each other. 18 What is an INDEX? An index in SQL is used to speed up the retrieval of data by allowing faster access to records. 19 What is normalization? What are its advantages? Normalization is the process of organizing data to reduce redundancy and improve data integrity. Advantages include reduced data redundancy, improved data integrity, and easier maintenance. 20 What is the difference between the DROP and TRUNCATE commands? DROP command removes a table from the database; it cannot be rolled back. TRUNCATE command removes all rows from a table without logging the individual row deletions. 21 What are the different types of normalization? 1NF, 2NF, 3NF, and BCNF. Each "NF" represents a further step to reduce data redundancy and dependency by organizing databases into tables and columns. 22 What is the ACID property in databases? ACID stands for Atomicity, Consistency, Isolation, Durability. These properties ensure that transactions are processed reliably and ensure the integrity of data within the database. 23 What do you mean by a trigger in SQL? A trigger in SQL is a special procedure that is automatically executed in response to certain events on a particular table, such as INSERT, UPDATE, or DELETE. 24 What are the different types of operators in SQL? SQL includes arithmetic, bitwise, comparison, compound, and logical operators, each serving different purposes in data manipulation and comparison. 25 Are NULL values the same as zero or blank spaces? No, NULL values represent a lack of data, whereas zero is a number and a blank space is a character. 26 What is the difference between a cross join and a natural join? Cross join produces a Cartesian product of two tables, whereas a natural join performs a join based on columns with the same name and compatible types in both tables. 27 What is a subquery in SQL? A subquery is a query within another query. The inner query executes first and its result is passed to the outer query. 28 What are the different types of subqueries? Correlated and non-correlated. A correlated subquery depends on data from the outer query, whereas a non-correlated subquery can be executed independently. 29 How can you count the number of records in a table? SELECT COUNT(*) FROM table_name; This command counts all rows in a table. 30 Write a SQL query to find names of employees starting with 'A'. SELECT * FROM Employees WHERE Name LIKE 'A%'; This query retrieves all employees whose names start with 'A'. 31 What is the need for group functions in SQL? Group functions, such as SUM, AVG, MAX, MIN, COUNT, are used to perform calculations on a set of rows and return a single result per group. 32 What is a relationship in SQL? A relationship is a connection between two tables based on a primary key to foreign key match. It can be one-to-one, one-to-many, or many-to-many. 33 How can you insert NULL values in a column while inserting data? You can insert NULL values by omitting the column from the list of columns in the INSERT statement or by explicitly using NULL in the VALUES list. 34 What is the difference between the BETWEEN and IN operators? BETWEEN filters the result within a range, whereas IN filters the result set against a discrete set of values. 35 What are SQL functions? SQL functions are built-in operations that can perform calculations, modify individual data items, manipulate the output, format data, or convert data types. 36 What is the need for the MERGE statement? MERGE is used for making conditional updates or insertions. If the row exists, it updates; if it does not, it inserts. 37 What is a recursive stored procedure in SQL? A recursive stored procedure calls itself until a specified boundary condition is met. It allows complex calculations and operations within the database. 38 What is a clause in SQL? Clauses like WHERE and HAVING help to filter the results of a query by specifying conditions that the data must meet. 39 What is the difference between HAVING and WHERE clause? WHERE filters rows before grouping, while HAVING filters groups after grouping. HAVING is typically used with aggregate functions. 40 What are the ways to execute dynamic SQL? Dynamic SQL can be executed using EXECUTE IMMEDIATE, using system stored procedures like sp_executesql, or by using query parameters. 41 What are the various levels of constraints? There are two levels: column level and table level. Column level applies to a single column, while table level applies to the whole table. 42 How can you fetch common records from two tables? Use the INTERSECT statement to find common records between two tables. Example: SELECT column_name FROM table1 INTERSECT SELECT column_name FROM table2; 43 List case manipulation functions in SQL. SQL provides functions like LOWER(), UPPER(), and INITCAP() to manipulate the case of strings. 44 What are the different set operators available in SQL? SQL supports set operators like UNION, INTERSECT, and MINUS to combine results of two or more queries. 45 What is an alias in SQL? An alias is a temporary name assigned to a table or column for the duration of a query. Example: SELECT c.customer_name AS name FROM customers c; 46 What are Aggregate and Scalar functions? Aggregate functions perform a calculation on a set of values and return a single value (e.g., SUM, AVG). Scalar functions return a value based on input value (e.g., SUBSTRING). 47 How can you fetch alternate records from a table? Use a query with the modulus operator. Example for even records: SELECT * FROM table WHERE (rownum%2) = 0; 48 Name the operator used for pattern matching in SQL. The LIKE operator is used for pattern matching in SQL, which can utilize % and _ as wildcards. 49 How can you select unique records from a table? Use the DISTINCT keyword. Example: SELECT DISTINCT column_name FROM table_name; 50 How can you fetch the first 5 characters of a string? Use the SUBSTRING function. Example: SELECT SUBSTRING(column_name, 1, 5) FROM table_name; 51 What is the main difference between SQL and PL/SQL? SQL is used for single query execution, while PL/SQL allows for a block of code with conditions, loops, and so on, to perform complex operations. 52 What is a view in SQL? A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table. 53 What are views used for? Views are used to simplify complex queries, improve security, and present data in a different format from the physical storage. 54 What is a stored procedure? A stored procedure is a prepared SQL code that you can save and reuse. In SQL Server, the saved code can be a routine, a script, or a query batch. 55 List some advantages and disadvantages of stored procedures. Advantages: better performance, reusability, reduced network traffic, enhanced security. Disadvantages: debugging can be complex, and they can lead to dependency on the database. 56 List all types of user-defined functions. There are three types: scalar functions, inline table-valued functions, and multi-statement table-valued functions. 57 What do you mean by collation? Collation refers to a set of rules that dictate how character data is sorted and compared. It affects the result of data operations. 58 What are the different types of collation sensitivity? There are four types: case sensitivity, accent sensitivity, kana sensitivity, and width sensitivity. 59 What are local and global variables? Local variables are only accessible within the function they are declared in, while global variables can be accessed throughout the application. 60 What is SQL AUTO_INCREMENT? AUTO_INCREMENT allows a unique number to be generated automatically when a new record is inserted into a table. 61 What is a data warehouse? A data warehouse is a large store of data accumulated from a wide range of sources within a company and used to guide management decisions. 62 What are the different authentication modes in SQL Server? SQL Server supports two authentication modes: Windows Authentication Mode and SQL Server and Windows Authentication Mode (Mixed Mode). 63 What are STUFF and REPLACE functions? STUFF function inserts a string into another string. REPLACE function replaces occurrences of a specified string. 64 How can you change authentication modes in SQL Server? You can change authentication modes using the SQL Server Management Studio or by altering the server properties and restarting the server. 65 What is the need for the MERGE statement? The MERGE statement is used to make conditional updates or insertions, a useful feature for syncing two tables or maintaining audit tables.