SQL Tutorial

 SQL Tutorial

SQL Tutorial (geeksforgeeks.org)

SQL is a language used to work with databases. It was made by IBM in the 1970s. With SQL, you can add, change, remove, and get data from databases like MySQL, Oracle, and others. Basically, SQL talks to databases.

SQL Guide

In this guide, you'll learn about SQL stuff like how to write queries, join data, prevent hacks, add data, and make tables. Anyone can learn SQL; you don't need any special knowledge beforehand.

What's a Database? Think of data as raw information. To make it tidy and useful, we put it in a database. A database is like a digital file cabinet where we keep and organize data. There's a special system, called a DBMS, that helps manage this data on computers.

SQL Basics:

Topic	Topic Content/Answer	SQL Code Example
Structured Query Language	- Language for managing databases.	SELECT * FROM users;
SQL	Datatypes	- Different kinds of data SQL can handle.
SQL	DDL, DML, TCL and DCL	- Commands for defining, manipulating, controlling, and transacting data.
SQL	TRANSACTIONS	- Managing multiple operations as a single unit.
SQL	VIEWS	- Virtual tables created from a SELECT statement.
SQL	Comments	- Notes in SQL that aren't executed.
SQL	Constraints	- Rules applied on data columns.
SQL	Creating Roles	- Define permissions for users.
SQL	Indexes	- Improves database search speeds.
SQL	SEQUENCES	- Automatically generate numbers.
SQL	Query Processing	- How SQL statements get processed.
CTE in SQL	- Temporary result set for use within a main query.	WITH cte_name AS (SELECT ...) SELECT ... FROM cte_name;
SQL Trigger	Student Database	- Automate actions based on database changes.
Book Management Database	- Managing books in a database.	SELECT * FROM books;
Introduction to NoSQL	- Databases that don't use standard SQL.	--NoSQL doesn't use SQL for operations.

SQL Clauses / Operators:

Topic	Answer (in Point Form)	SQL Example
SQL WITH clause	- Common Table Expressions (CTE)	WITH AdultUsers AS (SELECT user_id, name FROM Users WHERE age >= 18) SELECT * FROM AdultUsers;
SQL With Ties Clause	- Returns additional rows tied in ranking.	SELECT TOP 3 WITH TIES price FROM Products ORDER BY price DESC;
SQL Arithmetic Operators	- Used to perform mathematical operations.	SELECT product_name, quantity, price, quantity * price AS Total FROM Orders;
SQL Wildcard operators	- Used for pattern matching.	SELECT * FROM Users WHERE name LIKE 'A%';
SQL Intersect & Except clause	- Intersect: Combines rows. - Except: Removes rows.	SELECT user_id FROM Orders INTERSECT SELECT user_id FROM Users WHERE age > 21;
SQL USING Clause	- Specifies columns for JOIN operation.	SELECT * FROM Orders o JOIN Users u USING (user_id);
SQL MERGE Statement	- Merges rows from one table into another. - Not supported in all SQL versions.	Conceptual (depends on SQL version).
MERGE Statement in SQL Explained	- Combines the INSERT, UPDATE, and DELETE operations.	Conceptual explanation, no SQL needed.
SQL DDL, DML, DCL and TCL Commands	- DDL: Defines structure. - DML: Manages data. - DCL: Controls access.	CREATE TABLE NewUsers (user_id INT, username VARCHAR(50));
SQL CREATE DOMAIN	- Defines a user-defined data type.	Conceptual (specific to certain SQL versions).
SQL DESCRIBE Statement	- Shows structure of a table.	DESCRIBE Users;
SQL Case Statement	- Allows conditions in SQL queries.	SELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS AgeGroup FROM Users;
SQL UNIQUE Constraint	- Ensures unique values in a column.	ALTER TABLE Users ADD CONSTRAINT UC_Email UNIQUE (email);
SQL Create Table Extension	- Creates a new table based on the result of a query.	CREATE TABLE Minors AS SELECT * FROM Users WHERE age < 18;
SQL ALTER (RENAME)	- Renames a column or table.	ALTER TABLE Users RENAME COLUMN email TO emailAddress;
SQL ALTER (ADD, DROP, MODIFY)	- Adds/Drops/Modifies columns in a table.	ALTER TABLE Users ADD birthdate DATE;
SQL LIMIT Clause	- Restricts the number of rows returned.	SELECT * FROM Users LIMIT 10;
SQL INSERT IGNORE Statement	- Inserts rows, ignoring errors.	INSERT IGNORE INTO Users (user_id, name) VALUES (1, 'John');
SQL LIKE	- Searches for a pattern in columns.	SELECT * FROM Products WHERE product_name LIKE 'Toy%';
SQL SOME	- Compares a value to each value in a subquery or a list.	SELECT product_name FROM Products WHERE price > SOME (SELECT price FROM Products WHERE stock < 10);
SQL OFFSET-FETCH Clause	- Specifies an offset and fetches a limited number of rows.	SELECT * FROM Users ORDER BY name OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
SQL Except Clause	- Returns the difference between two SELECT statements.	SELECT user_id FROM Users EXCEPT SELECT user_id FROM Orders;

Topic	Answer	SQL Example
Combining aggregate and non-aggregate values using Joins and Over clause	Combine using JOIN & OVER clause.	sql SELECT name, AVG(price) OVER() as AvgPrice FROM Users JOIN Orders ON Users.user_id = Orders.user_id;
SQL | ALL and ANY	Used to compare a value to all or any values in another result set.	sql SELECT product_name FROM Products WHERE price > ALL (SELECT price FROM Products WHERE stock < 10);
SQL | EXISTS	Checks for existence of rows.	sql SELECT name FROM Users WHERE EXISTS (SELECT 1 FROM Orders WHERE Users.user_id = Orders.user_id);
SQL | GROUP BY	Groups rows that have the same values.	sql SELECT user_id, COUNT(order_id) FROM Orders GROUP BY user_id;
SQL | Union Clause	Combines the result set of two or more SELECT statements.	sql SELECT name FROM Users UNION SELECT product_name FROM Products;
SQL | Aliases	Used to rename a table or a column temporarily.	sql SELECT u.name AS UserName, o.product_name AS OrderedProduct FROM Users u JOIN Orders o ON u.user_id = o.user_id;
SQL | ORDER BY	Used to sort the result set.	sql SELECT name FROM Users ORDER BY age DESC;
SQL | SELECT TOP Clause	Used to specify the number of records to return.	sql SELECT TOP 3 product_name, price FROM Products ORDER BY price DESC;
SQL | UPDATE Statement	Used to modify existing records in a table.	sql UPDATE Users SET age = age + 1 WHERE name = 'John';
SQL | DELETE Statement	Used to delete existing records in a table.	sql DELETE FROM Users WHERE age < 18;
SQL | INSERT INTO Statement	Used to insert new records in a table.	sql INSERT INTO Users (user_id, name, age, email) VALUES (5, 'Jane', 30, 'jane@email.com');
SQL | AND and OR operators	Used to filter records based on multiple conditions.	sql SELECT * FROM Users WHERE age > 20 AND name LIKE 'J%';
SQL | WHERE Clause	Used to filter records.	sql SELECT * FROM Users WHERE age > 20;
SQL | Distinct Clause	Used to return only distinct values.	sql SELECT DISTINCT product_name FROM Orders;
SQL | SELECT Query	Used to select data from a database.	sql SELECT name, age FROM Users;
SQL | DROP, TRUNCATE	DROP: deletes a table. TRUNCATE: deletes data inside a table.	sql DROP TABLE Reviews; TRUNCATE TABLE Orders;
SQL | CREATE	Used to create an object in a database.	sql CREATE TABLE NewReviews (new_review_id INT, comment VARCHAR(255));
SQL | Join (Cartesian Join & Self Join)	Cartesian Join: Combines rows from two tables. Self Join: Join a table with itself.	sql SELECT * FROM Users u1, Users u2; SELECT a.name AS UserA, b.name AS UserB FROM Users a JOIN Users b ON a.user_id <> b.user_id;

Topic	Answer	SQL Example
Combining aggregate and non-aggregate values using Joins and Over clause	- Use aggregate functions with non-aggregate columns by employing the OVER() clause.	sql SELECT name, SUM(price) OVER() AS TotalSpent FROM Orders o JOIN Users u ON o.user_id = u.user_id;
Alternative Quote Operator	- The backtick (`) serves as an alternative quote operator in some SQL systems, mainly MySQL.	sql SELECT `name` FROM `Users`;
Concatenation Operator	- Joins multiple strings into one string. In SQL, you can use the `
MINUS Operator	- Returns all rows in the first query that are not returned in the second query.	sql SELECT user_id FROM Users MINUS SELECT user_id FROM Orders;
DIVISION	- Divides numbers.	sql SELECT price/quantity AS PricePerItem FROM Orders;
NOT Operator	- Excludes specified values.	sql SELECT * FROM Users WHERE NOT age >= 18;
BETWEEN & IN Operator	- BETWEEN: Selects values within a given range. <br> - IN: Selects values from a list.	sql SELECT * FROM Users WHERE age BETWEEN 18 AND 25; SELECT * FROM Users WHERE name IN ('John', 'Jane');
Join (Inner, Left, Right and Full Joins)	- INNER JOIN: Returns rows when there is a match in both tables.<br>- LEFT JOIN: Returns all rows from the left table, and the matched rows from the right table.<br>- RIGHT JOIN: Returns all rows from the right table, and the matched rows from the left table.<br>- FULL JOIN: Returns rows when there is a match in one of the tables.	sql SELECT u.name, o.product_name FROM Users u INNER JOIN Orders o ON u.user_id = o.user_id;
CHECK Constraint	- Ensures all values in a column satisfy a specific condition.	sql ALTER TABLE Users ADD CONSTRAINT CHK_Age CHECK (age>=18);

SQL-Injection:

Topic	Answer	SQL Example
SQL Injection	- A code injection technique where attackers insert malicious SQL code into input fields to gain unauthorized access or cause damage.	sql SELECT * FROM Users WHERE name = 'a'; DROP TABLE Users; --';
How to use SQLMAP to test a website for SQL Injection vulnerability	- SQLMAP is an open-source tool used to detect and exploit SQL injection vulnerabilities. <br>- Automates the process of detecting and exploiting SQL Injection vulnerabilities.	Conceptual topic, no direct SQL example.
Mitigation of SQL Injection Attack using Prepared Statements (Parameterized Queries)	- Uses precompiled SQL statements which ensure that user input is always treated as data, not executable code. <br>- Helps protect against SQL Injection attacks.	sql PreparedStatement ps = conn.prepareStatement("SELECT * FROM Users WHERE name = ?"); ps.setString(1, userName); ResultSet rs = ps.executeQuery();
Basic SQL Injection and Mitigation with Example	- Injecting malicious code into SQL statements via user input can lead to data theft, corruption, or unauthorized actions. <br>- Mitigation involves sanitizing user input and using parameterized queries.	Injection: <br>sql SELECT * FROM Users WHERE name = 'a' OR '1'='1'; <br>Mitigation: <br>sql PreparedStatement ps = conn.prepareStatement("SELECT * FROM Users WHERE name = ?"); ps.setString(1, userName);

SQL Functions:

Topic	Answer	SQL Example
Mathematical functions (SQRT, PI, SQUARE, ROUND, CEILING & FLOOR)	- Used to perform mathematical calculations in SQL.	sql SELECT SQRT(age), ROUND(age), CEILING(age), FLOOR(age) FROM Users;
Conversion Function	- Converts one datatype to another.	sql SELECT CAST(price AS INT) FROM Orders;
SQL general functions (NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL, NANVL)	- Functions that handle NULL values and perform logical operations.	sql SELECT COALESCE(email, 'No Email') FROM Users;
Conditional Expressions	- Allow logic to be introduced into SQL statements.	sql SELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS AgeGroup FROM Users;
Character Functions with Examples	- Functions that operate on string data types.	sql SELECT UPPER(name), LOWER(name), LENGTH(name) FROM Users;
Date Functions (Set-1)	- Perform operations on date data types.	sql SELECT CURRENT_DATE; (This is a conceptual example, as there's no specific date column mentioned.)
Date Functions (Set-2)	- Further operations on date data types.	sql SELECT CURRENT_TIMESTAMP;
LISTAGG	- Aggregates data from multiple rows into a single delimited string.	Conceptual, specific to certain SQL versions.
Aggregate functions	- Summarize data from multiple rows.	sql SELECT AVG(price) FROM Orders;
Functions (Aggregate and Scalar Functions)	- Aggregate functions summarize data, Scalar functions operate on a single value.	sql SELECT MAX(age), MIN(age) FROM Users;
Date functions	- Perform operations on date data types.	sql SELECT DAY(CURRENT_DATE);
NULL	- Represents missing or unknown data.	sql SELECT name FROM Users WHERE email IS NULL;
Numeric Functions	- Functions that operate on numeric data types.	sql SELECT ROUND(price, 2) FROM Orders;
String functions	- Operate on strings and return strings.	sql SELECT CONCAT(name, ' ', email) FROM Users;
Advanced Functions	- More complex SQL functions.	Conceptual topic, example would depend on the specific advanced function in question.

SQL Queries:

Topic	Answer	SQL Example
Joining three or more tables	- Combine rows from three or more tables based on related columns.	sql SELECT u.name, o.product_name, r.comment FROM Users u JOIN Orders o ON u.user_id = o.user_id JOIN Reviews r ON o.product_name = r.product_name;
How to Get the names of the table	- Retrieve the names of all tables in a database.	Conceptual, varies depending on SQL version (e.g., for MySQL: SHOW TABLES;).
Sub queries in From Clause	- Use a subquery as a table in the main query.	sql SELECT u.name FROM (SELECT name FROM Users WHERE age > 25) AS u;
Correlated Subqueries	- A subquery that references columns from the outer query.	sql SELECT name FROM Users u WHERE EXISTS (SELECT 1 FROM Orders o WHERE u.user_id = o.user_id AND product_name = 'Toy');
Top-N Queries	- Retrieve the top N records based on a particular order.	sql SELECT name, age FROM Users ORDER BY age DESC LIMIT 5;
SUB Queries	- A query embedded within another query.	sql SELECT name FROM Users WHERE user_id IN (SELECT user_id FROM Orders WHERE product_name = 'Toy');
How to print duplicate rows in a table?	- Display rows that have duplicate values in one or more columns.	sql SELECT name, COUNT(*) FROM Users GROUP BY name HAVING COUNT(*) > 1;
How to find Nth highest salary from a table	- Retrieve the Nth highest value from a column.	For 2nd highest: sql SELECT DISTINCT price FROM Orders o1 WHERE 2 = (SELECT COUNT(DISTINCT price) FROM Orders o2 WHERE o1.price <= o2.price);
DBMS Nested Queries in SQL	- Embed one query inside another query.	sql SELECT name FROM Users WHERE age > (SELECT AVG(age) FROM Users);
SQL query to find second highest salary?	- Retrieve the second highest value from a salary column.	(Similar to the Nth highest query above, but specifically for 2nd highest).

PL/SQL:

Topic	Answer	SQL/PLSQL Example
PL/SQL Introduction	- Procedural Language extension to SQL.<br>- Used for writing stored procedures in Oracle DBMS.	DECLARE v_name Users.name%TYPE := 'John'; BEGIN NULL; END;
Cursors in PL/SQL	- Used to retrieve multiple rows.<br>- Cursor holds the rows returned by the SQL statement.	DECLARE CURSOR c_users IS SELECT name FROM Users;
Sum Of Two Numbers in PL/SQL	- Use variables to store and add numbers.	DECLARE a NUMBER := 5; b NUMBER := 10; c NUMBER; BEGIN c := a + b; END;
Reverse a number in PL/SQL	- Procedure to reverse digits of a number.	Conceptual; requires a PL/SQL block with loops.
Factorial of a number in PL/SQL	- Recursively multiply number to get factorial.	Conceptual; requires a PL/SQL block with recursive function.
Print Patterns in PL/SQL	- Use loops to print patterns.	Conceptual; requires loops in PL/SQL.
Decision Making in PL/SQL	- Use IF, ELSE statements for decisions.	IF age > 18 THEN dbms_output.put_line('Adult'); END IF;
Oracle Pseudocolumn	- Columns like ROWID, ROWNUM.	SELECT ROWNUM, name FROM Users;
Procedures in PL/SQL	- Stored routines executed with CALL statement.	CREATE PROCEDURE add_user(p_name VARCHAR2) AS BEGIN /* Logic */ END;
Print different star patterns in SQL	- Use SQL and UNION to print patterns.	Conceptual; more about SQL creativity than direct PL/SQL.
GCD of two numbers in PL/SQL	- Find greatest common divisor.	Conceptual; requires a PL/SQL block.
Centered triangular number in PL/SQL	- A centered triangular number formula.	Conceptual; requires mathematical formula in PL/SQL.
Floyd’s triangle in PL/SQL	- Pattern printing.	Conceptual; requires loops in PL/SQL.
Convert km to meters & centimeters	- Multiplication for conversion.	DECLARE km NUMBER := 5; meters NUMBER; BEGIN meters := km * 1000; END;
Convert numbers into words in Pl/SQL	- Requires custom logic or Oracle's TO_CHAR.	Conceptual; may use TO_CHAR function with format.
Sum of digits of a number in PL/SQL	- Extract and add digits.	Conceptual; requires a PL/SQL block with loops.
Sum of digits equal to a given number	- Find numbers where sum of digits matches.	Conceptual; requires a PL/SQL block with loops.
Sum and average of three numbers	- Basic arithmetic operations.	DECLARE a NUMBER := 5; b NUMBER := 10; c NUMBER := 15; sum NUMBER; BEGIN sum := a + b + c; END;
String palindrome check in PL/SQL	- Check if string reads same forwards and backwards.	Conceptual; requires string manipulation in PL/SQL.
Count odd & even digits in a number	- Use modulo operator to determine odd/even.	Conceptual; requires a PL/SQL block.
Count vowels & consonants in a string	- Loop through string, count characters.	Conceptual; requires a PL/SQL block with loops.
Area & Perimeter of a circle in PL/SQL	- Use πr^2 for area and 2πr for perimeter.	Conceptual; requires mathematical formulas in PL/SQL.
Sum of first n natural numbers	- Use formula n(n+1)/2.	DECLARE n NUMBER := 5; sum NUMBER; BEGIN sum := n*(n+1)/2; END;
Area & Perimeter of Rectangle in PL/SQL	- Use lengthbreadth for area & 2(length+breadth) for perimeter.	Conceptual; requires mathematical formulas in PL/SQL.
Sum of first & last digit of a number	- Extract first and last digits and add.	Conceptual; requires number manipulation in PL/SQL.
Count characters & words in a string	- Use LENGTH and REGEXP_COUNT for counting.	Conceptual; requires string functions in PL/SQL.
Greatest of three numbers in PL/SQL	- Use IF-ELSE or GREATEST function.	DECLARE a NUMBER := 5; b NUMBER := 10; c NUMBER := 7; max NUMBER; BEGIN max := GREATEST(a,b,c); END;
Concatenation of strings in PL/SQL	- Use
PL/User Input	- Use & for taking user input in SQLPlus.	SELECT &userInput FROM Users;

MySQL:

Topic	Answer	SQL Example
My Regular expressions(Regexp)	- Used to search and manipulate strings.	sql SELECT name FROM Users WHERE name REGEXP '^[A-L]';
My Grant/Revoke Privileges	- Used to grant or revoke permissions.	sql GRANT SELECT ON Users TO 'newuser'@'localhost'; REVOKE SELECT ON Users FROM 'newuser'@'localhost';
My DATABASE() and CURRENT_USER() Functions	- Return the current database and user.	sql SELECT DATABASE(), CURRENT_USER();
My BIN() Function	- Convert a number to its binary representation.	sql SELECT BIN(12);
My IFNULL	- Returns the first expression if it is not NULL, otherwise returns the second.	sql SELECT IFNULL(age, 'Not Provided') AS Age FROM Users;
My LAST_DAY() Function	- Returns the last day of the month for a date.	sql SELECT LAST_DAY(NOW());
My RENAME USER	- Rename an existing MySQL user.	sql RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';
My DROP USER	- Removes one or more MySQL user accounts.	sql DROP USER 'user1'@'localhost';
My CREATE USER Statement	- Create a new MySQL user account.	sql CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
My Change User Password	- Update the password for a MySQL user.	sql SET PASSWORD FOR 'user'@'localhost' = 'newpassword';
PHP MySQL WHERE Clause	- Used to filter records based on specific condition(s).	sql SELECT * FROM Users WHERE age > 21;
PHP MySQL ORDER BY Clause	- Sort the result set in ascending or descending order.	sql SELECT name FROM Users ORDER BY age DESC;
PHP MySQL UPDATE Query	- Modify existing records in a table.	sql UPDATE Users SET email = 'newemail@example.com' WHERE user_id = 1;
PHP MySQL Delete Query	- Remove existing records from a table.	sql DELETE FROM Users WHERE user_id = 1;
PHP MySQL LIMIT Clause	- Specify the number of records to return.	sql SELECT * FROM Users LIMIT 5;
PHP MySQL Select Query	- Retrieve data from one or more tables.	sql SELECT name, email FROM Users;
PHP Inserting into MySQL database	- Add new records to a table.	sql INSERT INTO Users (name, age, email) VALUES ('John', 30, 'john@example.com');
PHP MySQL ( Creating Table )	- Define a new table with its structure.	sql CREATE TABLE NewTable (id INT, description VARCHAR(100));
PHP MySQL ( Creating Database )	- Create a new database.	sql CREATE DATABASE NewDatabase;

SQL Server:

Topic	Answer	SQL Example
SQL SERVER Conditional Statements	- Used to perform actions based on conditions.	sql SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS Status FROM Users;
SQL Server Identity	- An auto-increment feature used to generate unique numbers for the primary key column in a table.	sql CREATE TABLE NewUsers (ID int IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50));
SQL Server STUFF() Function	- Inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.	sql SELECT STUFF(email, CHARINDEX('@', email), 0, '@example.com') AS UpdatedEmail FROM Users;

Misc:

Topic	Answer	SQL Example
SQL using Python Set 1	- Using Python's database connectors (e.g., pyodbc, sqlite3) to interact with databases.	Conceptual, no direct SQL code
SQL using Python and SQLite Set 2	- SQLite is a lightweight database that can be integrated with Python without a server.	Conceptual, no direct SQL code
SQL using Python Set 3 (Handling large data)	- Use Python with databases for handling, manipulating, and querying large datasets.	Conceptual, no direct SQL code
Check if Table, View, Trigger, etc present in Oracle	- Check the existence of various objects in Oracle using metadata queries.	sql SELECT * FROM all_tables WHERE table_name = 'USERS';
Performing Database Operations in Java SQL CREATE, INSERT, UPDATE, DELETE and SELECT	- Java can connect to databases using JDBC (Java Database Connectivity) to perform various operations.	Conceptual, no direct SQL code
Difference between Simple and Complex View in SQL	- Simple View: Based on one table, no aggregations. - Complex View: Multiple tables, aggregations, JOINs.	Simple View: sql CREATE VIEW UserView AS SELECT user_id, name FROM Users;
Difference between Static and Dynamic SQL	- Static: Defined and fixed before the program is compiled. - Dynamic: Constructed and executed during runtime.	Conceptual, no direct SQL code
Having Vs Where Clause?	- WHERE: Filters rows before grouping. - HAVING: Filters after aggregations and grouping.	sql SELECT product_name, SUM(price) AS Total FROM Orders GROUP BY product_name HAVING SUM(price) > 100;
Inner Join Vs Outer Join	- Inner Join: Returns rows when there is a match in both tables. - Outer Join: Also returns unmatched rows from one/both table(s).	Inner Join: sql SELECT * FROM Users u JOIN Orders o ON u.user_id = o.user_id;
Difference between SQL and NoSQL	- SQL: Relational, structured schema, uses SQL queries. - NoSQL: Non-relational, dynamic schema, various query methods.	Conceptual, no direct SQL code