DBMS: Week 05 - Introduction to SQL Query

International Islamic University H-10, Islamabad, Pakistan Database Managements Systems Week 05 Introduction to SQL Engr. Rashid Farid Chishti http://youtube.com/rfchishti http://sites.google.com/site/chisht i

 Definition: Structured Query Language (SQL) is used to interact with and manipulate databases.  Purpose:  Query, update, and manage data  Define database structures (tables, schemas)  Control access and permissions  Key Features:  Simple and intuitive syntax  Standardized across most RDBMS  Highly efficient for large data sets What is SQL?

 Data Definition Language (DDL): Define and manage database schema  Commands: CREATE, ALTER, RENAME, TRUNCATE, DROP  Data Query Language (DQL): Query and retrieve data  Command: SELECT, DISTINCT  Data Manipulation Language (DML): Manipulate data in tables  Commands: INSERT, UPDATE, DELETE  Data Control Language (DCL): Manage access permissions to users  Commands: GRANT, REVOKE  Transaction Control Language (TCL): manages database transactions. Commands: START, COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION Categories of SQL Commands

 Integer Data Types: Used for storing exact numbers. Data Types in MySQL Storage (Bytes) Range (Signed) Range (Unsigned) TINYINT 1 -128 to 127 0 to 255 SMALLINT 2 -32,768 to 32,767 0 to 65,535 MEDIUMINT 3 -8,388,608 to 8,388,607 0 to 16,777,215 INT (INTEGER) 4 -2,147,483,648 to 2,147,483,647 0 to 4,294,967,295 BIGINT 8 -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18,446,744,073,709,55 1,615

 Fixed-Point Types : Used for storing exact numbers with decimal places.  Floating-Point Types : Used for storing approximate numbers. Data Types in MySQL Data Type Storage (Bytes) Description DECIMAL(p,s) (NUMERIC) Varies Stores exact decimal values. p (precision) is total digits, s (scale) is decimal places. e.g., DECIMAL(10,2) stores up to 10 digits with 2 decimal places. FIXED Varies Synonym for DECIMAL Data Type Storage (Bytes) Description FLOAT(p) 4 (for p ≤ 24) 8 (for p > 24) Single-precision floating-point number. DOUBLE(REAL) 8 Double-precision floating-point number.

 Bitwise Data Type : Used for storing binary values.  Date and Time Data Types: Used for storing date and time. Data Types in MySQL Data Type Storage (Bytes) Description BIT(n) Varies Stores binary values (BIT(8) stores 8 bits). Data Type Storage (Bytes) Format Description DATE 3 YYYY-MM-DD Stores only the date. Range: 1000-01-01 to 9999-12-31 DATETIME 8 YYYY-MM-DD HH:MM:SS Stores date and time. TIMESTAMP 4 YYYY-MM-DD HH:MM:SS Stores date and time (auto-updates). TIME 3 HH:MM:SS Stores only time. YEAR 1 YYYY Stores only year Range: 1901 to 2155.

 String (Character) Data Types: Used for storing text data. Data Types in MySQL Data Type Storage (Bytes) Description CHAR(n) Fixed (0-255) Fixed-length string CHAR(10) always uses 10 bytes. VARCHAR(n) Varies (0-65,535) Variable-length string VARCHAR(10) uses actual length + 1 byte. TINYTEXT Up to 255 Small text (up to 255 characters). TEXT Up to 65,535 Medium Text MEDIUMTEXT Up to 16,777,215 Large Text LONGTEXT Up to 4GB Very Large Text

 Binary Types: Used For Storing Files & Encrypted Data.  Binary objects can be images, audio, videos, etc. Data Types in MySQL Data Type Storage (Bytes) Description TINYBLOB Up to 255 Small binary objects. BLOB Up to 65,535 Medium binary object. MEDIUMBLOB Up to 16,777,215 Large binary object. LONGBLOB Up to 4GB Very large binary object.

 DDL stands for DATA DEFINATION LANGUAGE.  DDL changes the structure of the table like creating, deleting & altering a table.  All the command of DDL are auto-committed that means it permanently save all the changes in the database  DDL Commands:  CREATE: Defines new tables, databases, indexes  ALTER: Modifies existing tables or databases  ADD, MODIFY, CHANGE, DROP, RENAME.  TRUNCATE: Deletes all rows from a table  DROP: Deletes tables or databases Data Definition Language (DDL)

CREATE: Creates a new database, table, index, or view RENAME: Renames an existing table TRUNCATE: Removes all records from a table but keeps its structure DROP: Deletes a database, table, index, or view permanently Data Definition Language (DDL) Commands ALTER: Modifies an existing table, column, or database structure ADD COLUMN: to add a new column to an existing table. CHANGE COLUMN: modify an existing column's name, data type MODIFY COLUMN: change the data type, constraints, or position of an existing column without renaming it. DROP COLUMN: Delete a Column, Once dropped, the column and its data cannot be recovered unless a backup exists.

 The CREATE command is used to define and create database objects such as databases, tables, indexes, views, triggers, stored procedures, and users.  Creating a Database  Creating a Table Creating Tables with CREATE Command DROP TABLE IF EXISTS Student; CREATE TABLE Student ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, age INT, email VARCHAR(100) UNIQUE ); CREATE DATABASE student_db; USE student_db;

 The ALTER command in MySQL is used to modify an existing database object such as a table, column, index, or constraint.  Adding a new Column  Verify column addition  Renaming a Column  Verify changes  Dropping a Column  Verify changes Modifying Tables with ALTER Command ALTER TABLE Student ADD COLUMN gender VARCHAR(10); ALTER TABLE Student MODIFY COLUMN age SMALLINT; ALTER TABLE Student DROP COLUMN age; DESCRIBE Student; DESCRIBE Student; DESCRIBE Student;

 Renaming a column  Verify  Renaming a table  Again renaming a table and verify Modifying Tables with ALTER Command ALTER TABLE Student CHANGE COLUMN name full_name VARCHAR(150); DESCRIBE Student; RENAME TABLE Student TO Students; SHOW TABLES; RENAME TABLE Students TO Student; SHOW TABLES;

 The TRUNCATE command in MySQL is used to remove all rows from a table while resetting the auto-increment counter (if applicable).  Key Features:  Deletes All Data – Unlike DELETE, which requires a WHERE clause to remove specific rows, TRUNCATE removes all rows.  Faster than DELETE – It does not log individual row deletions, making it more efficient.  Resets AUTO_INCREMENT Counter – If the table has an AUTO_INCREMENT column, it resets to 1.  Does Not Activate Triggers – Unlike DELETE, TRUNCATE does not execute BEFORE DELETE or AFTER DELETE triggers.  Cannot Be Rolled Back in MySQL – Since it is a DDL command, it commits immediately and cannot be undone with ROLLBACK.  Truncate a Table: Using TRUNCATE Command (1/2) TRUCCATE TABLE

 Add some data first:  Show data:  Truncate a table:  Show data again: Using TRUNCATE Command (2/2) TRUCCATE TABLE Student; INSERT INTO Student VALUE (101,'Rashid','chishti@gmail.com','male'); INSERT INTO Student (full_name, email, gender) VALUE ('Farid','farid@gmail.com','male'); SELECT * FROM Student; SELECT * FROM Student;

Key Differences: TRUNCATE vs. DELETE vs. DROP Feature TRUNCATE DELETE DROP Removes all rows ✅ Yes ✅ Yes (if no WHERE) ✅ Yes Can use WHERE clause ❌ No ✅ Yes ❌ No Resets auto- increment ✅ Yes ❌ No ✅ Yes Affects table structure ❌ No ❌ No ✅ Yes (removes table) Triggers execution ❌ No ✅ Yes ❌ No Can be rolled back ❌ No (DDL) ✅ Yes (if inside TRANSACTION) ❌ No Speed ⚡ Faster 🐢 Slower ⚡ Fastest

 The DROP command in MySQL is used to permanently remove a database object, such as a table, database, index, or view. This operation cannot be rolled back.  Drop a Table and verify deletion of table:  Drop a database and verify deletion:  Removing a primary key constraint  First Remove AUTO_INCREMENT  Then remove PRIMARY KEY  Change back to primary key with auto increment Using DROP Command DROP TABLE IF EXISTS student; SHOW TABLES; DROP DATABASE IF EXISTS student_db; SHOW DATABASES; ALTER TABLE Student MODIFY ID INT; ALTER TABLE Student DROP PRIMARY KEY; DESCRIBE Student; ALTER TABLE Student MODIFY ID INT AUTO_INCREMENT PRIMARY

 Data Query Language (DQL) is a subset of SQL (Structured Query Language) that focuses on retrieving data from a database. It consists of commands used to query and fetch data from relational databases.  Key Features of DQL:  Primarily deals with retrieving data.  Uses the SELECT statement to fetch records.  Supports filtering, sorting, grouping, and aggregation.  Works with clauses like WHERE, ORDER BY, GROUP BY, HAVING, and JOIN to refine queries. Data Query Language (DQL)

ASC: Ascending Order DESC: Descending Order ORDER BY: Column Name(s) HAVING: Condition(s) GROUP BY : Column Name(s) WHERE: Condition(s) JOIN: table ON table.column FROM: Name of Table(s) DISTINCT: Name of Table SELECT: retrieve data from one or more tables in a database. Data Query Language (DQL) Command LIMIT: Number

First Make a Student Table DROP TABLE IF EXISTS Student; CREATE TABLE Student ( id INT AUTO_INCREMENT PRIMARY KEY, roll_no VARCHAR(20) NOT NULL, name VARCHAR(50) NOT NULL, age INT NOT NULL CHECK(Age>=0), dob DATE NOT NULL DEFAULT (CURDATE()), degree VARCHAR(30) NOT NULL ); DROP DATABASE IF EXISTS student_db; CREATE DATABASE student_db; USE student_db;

First INSERT Some Data INSERT INTO Student(ID, Roll_No, Name, Age, DoB, Degree) VALUES (1, '3-FET/BSCE/F22', 'Nawal Mudassar', 21, '2003-11-09', 'BSCE' ), (2, '3-FET/BSCET/F23', 'Waleed Khan', 20, '2005-02-18', 'BSCET'), (3, '4-FET/BSCE/F22', 'M. Zain Tariq', 21, '2003-08-04', 'BSCE' ), (4, '4-FET/BSCET/F23', 'Abdul Manan', 22, '2002-10-23', 'BSCET'), (5, '6-FET/BSCE/F22', 'Haseen Ullah', 19, '2005-03-16', 'BSCE' ), (6, '6-FET/BSCET/F23', 'Saif Ullah', 19, '2005-11-27', 'BSCET'), TRUNCATE Student; # Delete all records from Student Table

 The SELECT statement is used to retrieve data from one or more tables.  Retrieve all columns from a table  Retrieve specific columns  Overriding column names  Sort data using ORDER BY Using SELECT Command (1/2) SELECT * FROM student; SELECT roll_no, email FROM student; SELECT roll_no AS 'Roll Number' FROM student; SELECT roll_no AS 'Roll Number', name AS 'Student’s Name' FROM student; SELECT * FROM student ORDER BY name DESC; SELECT * FROM student ORDER BY name ASC; SELECT age, name FROM student ORDER BY age DESC, name ASC;

 Limit number of rows.  Get unique values.  Filter data using WHERE Using SELECT Command (1/2) SELECT * FROM student LIMIT 2; SELECT DISTINCT Age FROM student; SELECT * FROM student WHERE ID = 101; SELECT * FROM student ORDER BY name DESC LIMIT 3; SELECT * FROM student ORDER BY name ASC LIMIT 3;

DBMS: Week 05 - Introduction to SQL Query

More Related Content

Similar to DBMS: Week 05 - Introduction to SQL Query

More from RashidFaridChishti

Recently uploaded

DBMS: Week 05 - Introduction to SQL Query