Introduction to database and sql fir beginers

INTRODUCTION TO SQL RESHMI PRADEEP

index  What is SQL  What Can SQL do?  SQL Commands  SQL Aggregate Functions  SQL Joins

What is SQL? • SQL stands for Structured Query Language • SQL lets you access and manipulate databases • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do? • SQL can execute queries against a database • SQL can retrieve data from a database • SQL can insert records in a database • SQL can update records in a database • SQL can delete records from a database • SQL can create new databases • SQL can create new tables in a database • SQL can create stored procedures in a database • SQL can create views in a database • SQL can set permissions on tables, procedures, and views

SQL Commands • Types of SQL Commands: • DDL (Data Definition Language): • DML (Data Manipulation Language): • DCL (Data Control Language • TCL (Transaction Control Language)

Data Definition Language (DDL) Commands Data Definition Language, is a subset of SQL (Structured Query Language) commands used to define and modify the database structure. These commands are used to create, alter, and delete database objects like tables, indexes, and schemas. 1. CREATE: Creates a new table or database. 2. ALTER: Modifies an existing database object. 3. DROP: Deletes an entire table, database, or other objects. 4. TRUNCATE: Removes all records from a table, deleting the space allocated for the records. DDL commands play a crucial role in defining the database schema.

The primary DDL commands in SQL include: • CREATE: This command is used to create a new database object. For example, creating a new table, a view, or a database. Syntax for creating a table: CREATE TABLE table_name (column1 datatype, column2 datatype, ...); ALTER: This command is used to modify an existing database object, such as adding, deleting, or modifying columns in an existing table. Syntax for adding a column in a table: ALTER TABLE table_name ADD column_name datatype; Syntax for modifying a column in a table: ALTER TABLE table_name MODIFY COLUMN column_name datatype;

• TRUNCATE: This command is used to delete all data from a table, but the structure of the table remains. It’s a fast way to clear large data from a table. Syntax: TRUNCATE TABLE table_name; • COMMENT: Used to add comments to the data dictionary. Syntax: COMMENT ON TABLE table_name IS 'This is a comment.’; • RENAME: Used to rename an existing database object.

Data Manipulation Language (DML) Data Manipulation Language (DML) is a subset of SQL commands used for adding (inserting), deleting, and modifying (updating) data in a database. DML commands are crucial for managing the data within the tables of a database. 1. SELECT: Retrieves data from the database. 2. INSERT: Adds new data to a table. 3. UPDATE: Modifies existing data within a table. 4. DELETE: Removes data from a table.

INSERT: This command is used to add new rows (records) to a table. Syntax: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); UPDATE: This command is used to modify the existing records in a table. Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; The WHERE clause specifies which records should be updated. Without it, all records in the table will be updated. DELETE: This command is used to remove one or more rows from a table. Syntax: DELETE FROM table_name WHERE condition; Like with UPDATE, the WHERE clause specifies which rows should be deleted. Omitting the WHERE clause will result in all rows being deleted

SELECT: Although often categorized separately, the SELECT command is sometimes considered part of DML as it is used to retrieve data from the database. Syntax: SELECT column1, column2, ... FROM table_name WHERE condition; The SELECT statement is used to query and extract data from a table, which can then be used for various purposes.

Data Control Language (DCL) Commands in SQL Data Control Language (DCL) is a subset of SQL commands used to control access to data in a database. DCL is crucial for ensuring security and proper data management, especially in multi-user database environments. • GRANT: This command is used to give users access privileges to the database. These privileges can include the ability to select, insert, update, delete, and so on, over database objects like tables and views. Syntax: GRANT privilege_name ON object_name TO user_name; For example, GRANT SELECT ON employees TO user123; gives user123 the permission to read data from the employees table.

• REVOKE: This command is used to remove previously granted access privileges from a user. Syntax: REVOKE privilege_name ON object_name FROM user_name; For example, REVOKE SELECT ON employees FROM user123; would remove user123‘s permission to read data from the employees table.

Transaction Control Language (TCL) Transaction Control Language, is a subset of SQL used to manage database transactions. TCL commands ensure data integrity by allowing you to control when changes to the database are saved permanently or rolled back. COMMIT:The COMMIT command is used to save changes made during a transaction to the database permanently: BEGIN; -- SQL statements COMMIT;

ROLLBACK:The ROLLBACK command is used to undo changes made during a transaction: BEGIN; -- SQL statements ROLLBACK; This example begins a transaction, performs SQL statements, and then rolls back the changes, restoring the database to its previous state. SAVEPOINT:The SAVEPOINT command allows you to set a point within a transaction to which you can later roll back: BEGIN; -- SQL statements SAVEPOINT my_savepoint; -- More SQL statements ROLLBACK TO my_savepoint; This example creates a savepoint and later rolls back to that point,

SQL Aggregate Functions • MIN() - returns the smallest value within the selected column Syntax: SELECT MIN(column_name) FROM table_name WHERE condition; • Example: SELECT MIN(Price)FROM Products; • MAX() - returns the largest value within the selected column Syntax: SELECT MAX(column_name) FROM table_name WHERE condition; • Example: SELECT MAX(Price)FROM Products;

• COUNT() - returns the number of rows in a set Syntax SELECT COUNT(column_name) FROM table_name WHERE condition; • Example: SELECT COUNT(*) FROM Products; • SUM() - returns the total sum of a numerical column Syntax SELECT SUM(column_name) FROM table_name WHERE condition; • Example:SELECT SUM(Quantity) FROM OrderDetails; • AVG() - returns the average value of a numerical column • Syntax SELECT AVG(column_name) FROM table_name WHERE condition; Example:SELECT AVG(Price) FROM Products;

SQL Joins A JOIN clause is used to combine rows from two or more tables, based on a related column between them. SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Different Types of SQL JOINs • (INNER) JOIN: Returns records that have matching values in both tables • LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table • RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table • FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

The SQL SELECT DISTINCT Statement The SELECT DISTINCT statement is used to return only distinct (different) values. Syntax SELECT DISTINCT column1, column2, ... FROM table_name; Example

SQL WHERE Clause The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition. Example SQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. Example

Introduction to database and sql fir beginers

More Related Content

Similar to Introduction to database and sql fir beginers

Recently uploaded

Introduction to database and sql fir beginers