Download SQL TUTORIAL and database................................................................ and more Summaries Database Programming in PDF only on Docsity!
SQL
(Notes by Apna College)
What is Database? Database is a collection of interrelated data. What is DBMS? DBMS ( Database Management System ) is software used to create, manage, and organize databases. What is RDBMS? ● RDBMS (Relational Database Management System) - is a DBMS based on the concept of tables (also called relations). ● Data is organized into tables (also known as relations) with rows (records) and columns (attributes). ● Eg - MySQL, PostgreSQL, Oracle etc. What is SQL? SQL is Structured Query Language - used to store, manipulate and retrieve data from RDBMS. (It is not a database, it is a language used to interact with database) We use SQL for CRUD Operations : ● CREATE - To create databases, tables, insert tuples in tables etc ● READ - To read data present in the database. ● UPDATE - Modify already inserted data. ● DELETE - Delete database, table or specific data point/tuple/row or multiple rows. *Note - SQL keywords are NOT case sensitive. Eg: select is the same as SELECT in SQL. SQL v/s MySQL SQL is a language used to perform CRUD operations in Relational DB, while MySQL is a RDBMS that uses SQL.
SQL Data Types In SQL, data types define the kind of data that can be stored in a column or variable. To See all data types of MYSQL, visit : https://dev.mysql.com/doc/refman/8.0/en/data-types.html Here are the frequently used SQL data types: DATATYPE DESCRIPTION USAGE CHAR string(0-255), can store characters of fixed length CHAR(50) VARCHAR string(0-255), can store characters up to given length VARCHAR(50) BLOB string(0-65535), can store binary large object BLOB(1000) INT integer( -2,147,483,648 to 2,147,483,647 ) INT TINYINT integer(-128 to 127) TINYINT BIGINT integer( -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 )
BIGINT
BIT can store x-bit values. x can range from 1 to 64 BIT(2) FLOAT Decimal number - with precision to 23 digits FLOAT DOUBLE Decimal number - with 24 to 53 digits DOUBLE BOOLEAN Boolean values 0 or 1 BOOLEAN DATE date in format of YYYY-MM-DD ranging from 1000-01-01 to 9999-12-
DATE
TIME HH:MM:SS TIME
YEAR year in 4 digits format ranging from 1901 to 2155 YEAR *Note - CHAR is for fixed length & VARCHAR is for variable length strings. Generally, VARCHAR is better as it only occupies necessary memory & works more efficiently. We can also use UNSIGNED with datatypes when we only have positive values to add. Eg - UNSIGNED INT Types of SQL Commands:
● CREATE INDEX:
○ Used to create an index on one or more columns in a table.
○ Improves query performance by enabling faster data retrieval.
○ Example: CREATE INDEX idx_employee_name ON employees (name);
● DROP INDEX:
○ Used to remove an existing index from a table.
○ Example: DROP INDEX idx_employee_name;
● CREATE CONSTRAINT:
○ Used to define constraints that ensure data integrity.
○ Constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL,
and CHECK.
○ Example: ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN
KEY (customer_id) REFERENCES customers(id);
● DROP CONSTRAINT:
○ Used to remove an existing constraint from a table.
○ Example: ALTER TABLE orders DROP CONSTRAINT fk_customer;
● TRUNCATE TABLE:
○ Used to delete the data inside a table, but not the table itself.
○ Syntax – TRUNCATE TABLE table_name
2. DATA QUERY/RETRIEVAL LANGUAGE (DQL or DRL)
DQL (Data Query Language) is a subset of SQL focused on retrieving data from databases. The SELECT statement is the foundation of DQL and allows us to extract specific columns from a table. ● SELECT: The SELECT statement is used to select data from a database.
Syntax: SELECT column1, column2, ... FROM table_name; Here, column1, column2, ... are the field names of the table. If you want to select all the fields available in the table, use the following syntax: SELECT * FROM table_name; Ex: SELECT CustomerName, City FROM Customers; ● WHERE: The WHERE clause is used to filter records. Syntax: SELECT column1, column2, ... FROM table_name WHERE condition; Ex: SELECT * FROM Customers WHERE Country='Mexico'; Operators used in WHERE are: = : Equal
: Greater than < : Less than = : Greater than or equal <= : Less than or equal <> : Not equal. Note: In some versions of SQL this operator may be written as != ● AND, OR and NOT:
- The WHERE clause can be combined with AND, OR, and NOT operators.
- The AND and OR operators are used to filter records based on more than one condition:
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
- The NOT operator displays a record if the condition(s) is NOT TRUE. Syntax:
WHERE CustomerName LIKE 'a_%'
- Finds any values that start with "a" and are at least 2 characters in length WHERE CustomerName LIKE 'a__%'
- Finds any values that start with "a" and are at least 3 characters in length WHERE ContactName LIKE 'a%o'
- Finds any values that start with "a" and ends with "o" ● IN: Filters results based on a list of values in the WHERE clause. Example: SELECT * FROM products WHERE category_id IN (1, 2, 3); ● BETWEEN : Filters results within a specified range in the WHERE clause. Example: SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-06-30'; ● IS NULL : Checks for NULL values in the WHERE clause. Example: SELECT * FROM customers WHERE email IS NULL; ● AS : Renames columns or expressions in query results. Example: SELECT first_name AS "First Name", last_name AS "Last Name" FROM employees; ● ORDER BY The ORDER BY clause allows you to sort the result set of a query based on one or more columns. Basic Syntax:
- The ORDER BY clause is used after the SELECT statement to sort query results.
- Syntax: SELECT column1, column2 FROM table_name ORDER BY column [ASC|DESC]; Ascending and Descending Order:
- By default, the ORDER BY clause sorts in ascending order (smallest to largest).
- You can explicitly specify descending order using the DESC keyword.
- Example: SELECT product_name, price FROM products ORDER BY price DESC; Sorting by Multiple Columns:
- You can sort by multiple columns by listing them sequentially in the ORDER BY clause.
- Rows are first sorted based on the first column, and for rows with equal values, subsequent columns are used for further sorting.
- Example: SELECT first_name, last_name FROM employees ORDER BY last_name, first_name; Sorting by Expressions:
- It's possible to sort by calculated expressions, not just column values.
- Example: SELECT product_name, price, price * 1.1 AS discounted_price FROM products ORDER BY discounted_price; Sorting NULL Values:
- By default, NULL values are considered the smallest in ascending order and the largest in descending order.
- You can control the sorting behaviour of NULL values using the NULLS FIRST or NULLS LAST options.
- Example: SELECT column_name FROM table_name ORDER BY column_name NULLS LAST; Sorting by Position:
- Instead of specifying column names, you can sort by column positions in the ORDER BY clause.
- Example: SELECT product_name, price FROM products ORDER BY 2 DESC, 1 ASC; ● GROUP BY The GROUP BY clause in SQL is used to group rows from a table based on one or more columns. Syntax:
Computes the average of numeric values in a group or result set.
- MAX(): Finds the maximum value in a group or result set.
- MIN(): Retrieves the minimum value in a group or result set.
3. DATA MANIPULATION LANGUAGE
Data Manipulation Language (DML) in SQL encompasses commands that manipulate data within a database. DML allows you to insert, update, and delete records, ensuring the accuracy and currency of your data. ● INSERT:
- The INSERT statement adds new records to a table.
- Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
- Example: INSERT INTO employees (first_name, last_name, salary) VALUES ('John', 'Doe', 50000);
● UPDATE :
- The UPDATE statement modifies existing records in a table.
- Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
- Example: UPDATE employees SET salary = 55000 WHERE first_name = 'John';
● DELETE :
- The DELETE statement removes records from a table.
- Syntax: DELETE FROM table_name WHERE condition;
- Example: DELETE FROM employees WHERE last_name = 'Doe';
4. Data Control Language (DCL)
Data Control Language focuses on the management of access rights, permissions, and security-related aspects of a database system. DCL commands are used to control who can access the data, modify the data, or perform administrative tasks within a database. DCL is an important aspect of database security, ensuring that data remains protected and only authorised users have the necessary privileges. There are two main DCL commands in SQL: GRANT and REVOKE.
1. GRANT: The GRANT command is used to provide specific privileges or permissions to users or roles. Privileges can include the ability to perform various actions on tables, views, procedures, and other database objects. Syntax: GRANT privilege_type ON object_name TO user_or_role; In this syntax:
● privilege_type refers to the specific privilege or permission being granted (e.g.,
SELECT, INSERT, UPDATE, DELETE).
● object_name is the name of the database object (e.g., table, view) to which the
privilege is being granted.
● user_or_role is the name of the user or role that is being granted the privilege.
Example: Granting SELECT privilege on a table named "Employees" to a user named "Analyst": GRANT SELECT ON Employees TO Analyst;
2. REVOKE: The REVOKE command is used to remove or revoke specific privileges or permissions that have been previously granted to users or roles. Syntax: REVOKE privilege_type ON object_name
It makes all the changes applied to the database since the last COMMIT or ROLLBACK command permanent. Once a COMMIT is executed, the transaction is considered successful, and the changes are made permanent. Example: Committing changes made during a transaction: UPDATE Employees SET Salary = Salary * 1. WHERE Department = 'Sales'; COMMIT;
2. ROLLBACK: The ROLLBACK command is used to undo changes made during a transaction. It reverts all the changes applied to the database since the transaction began. ROLLBACK is typically used when an error occurs during the execution of a transaction, ensuring that the database remains in a consistent state. Example: Rolling back changes due to an error during a transaction: BEGIN; UPDATE Inventory SET Quantity = Quantity - 10 WHERE ProductID = 101; -- An error occurs here ROLLBACK; 3. SAVEPOINT: The SAVEPOINT command creates a named point within a transaction, allowing you to set a point to which you can later ROLLBACK if needed. SAVEPOINTs are useful when you want to undo part of a transaction while preserving other changes. Syntax: SAVEPOINT savepoint_name; Example: Using SAVEPOINT to create a point within a transaction: BEGIN;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 123; SAVEPOINT before_withdrawal; UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 456; -- An error occurs here ROLLBACK TO before_withdrawal; -- The first update is still applied COMMIT; TCL and Transaction Management: Transaction Control Language (TCL) commands are vital for managing the integrity and consistency of a database's data. They allow you to group related changes into transactions, and in the event of errors, either commit those changes or roll them back to maintain data integrity. TCL commands are used in combination with Data Manipulation Language (DML) and other SQL commands to ensure that the database remains in a reliable state despite unforeseen errors or issues. JOINS In a DBMS, a join is an operation that combines rows from two or more tables based on a related column between them. Joins are used to retrieve data from multiple tables by linking them together using a common key or column. Types of Joins:
101 1 Laptop 102 3 Smartphone 103 2 Headphones Inner Join Query: SELECT Customers.CustomerName, Orders.Product FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; Result: CustomerName Product Alice Laptop Bob Headphones Carol Smartphone 2) Outer Join Outer joins combine data from two or more tables based on a specified condition, just like inner joins. However, unlike inner joins, outer joins also include rows that do not have matching values in both tables. Outer joins are particularly useful when you want to include data from one table even if there is no corresponding match in the other table. Types : There are three types of outer joins: left outer join, right outer join, and full outer join.
1. Left Outer Join (Left Join): A left outer join returns all the rows from the left table and the matching rows from the right table.
If there is no match in the right table, the result will still include the left table's row with NULL values in the right table's columns. Example: SELECT Customers.CustomerName, Orders.Product FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; Result: CustomerName Product Alice Laptop Bob Headphones Carol Smartphone NULL Monitor In this example, the left outer join includes all rows from the Customers table. Since there is no matching customer for the order with OrderID 103 (Monitor), the result includes a row with NULL values in the CustomerName column.
2. Right Outer Join (Right Join): A right outer join is similar to a left outer join, but it returns all rows from the right table and the matching rows from the left table. If there is no match in the left table, the result will still include the right table's row with NULL values in the left table's columns. Example: Using the same Customers and Orders tables. SELECT Customers.CustomerName, Orders.Product FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID; Result:
NULL Monitor NULL Keyboard In this full outer join example, all rows from both tables are included in the result. Both non-matching rows from the Customers and Orders tables are represented with NULL values. 3) Cross Join A cross join, also known as a Cartesian product, is a type of join operation in a Database Management System (DBMS) that combines every row from one table with every row from another table. Unlike other join types, a cross join does not require a specific condition to match rows between the tables. Instead, it generates a result set that contains all possible combinations of rows from both tables. Cross joins can lead to a large result set, especially when the participating tables have many rows. Syntax: SELECT columns FROM table CROSS JOIN table2; In this syntax:
● columns refers to the specific columns you want to retrieve from the cross-joined
tables.
● table1 and table2 are the names of the tables you want to combine using a cross
join. Example: Consider two tables: Students and Courses. Students Table: StudentID StudentName 1 Alice
2 Bob Courses Table: CourseID CourseName 101 Maths 102 Science Cross Join Query: SELECT Students.StudentName, Courses.CourseName FROM Students CROSS JOIN Courses; Result: StudentName CourseName Alice Maths Alice Science Bob Maths Bob Science In this example, the cross join between the Students and Courses tables generates all possible combinations of rows from both tables. As a result, each student is paired with each course, leading to a total of four rows in the result set. 4) Self Join A self join involves joining a table with itself. This technique is useful when a table contains hierarchical or related data and you need to compare or analyse rows within the same table.
