Introduction to Structured Query Language (SQL) Chapter Notes | Informatics Practices for Class 11 - Humanities/Arts PDF Download

Introduction

• Relational Database Management Systems (RDBMS) like MySQL, Microsoft SQL Server, PostgreSQL, and Oracle allow creation of databases with relations.
• RDBMS enables linking one or more relations for efficient querying to store, retrieve, and manipulate data.
• This chapter focuses on using MySQL to create, populate, and query databases.

Structured Query Language (SQL)

• In file systems, application programs are written to access data, but RDBMS use a special programming language called a query language.
• SQL is the most popular query language used by major RDBMS like MySQL, Oracle, and SQL Server.
• SQL is easy to learn as its statements consist of descriptive English words and are case-insensitive.
• SQL allows efficient and easy creation and interaction with databases.
• Users specify what data to retrieve without needing to define how to retrieve it, and SQL handles the process.
• Beyond querying, SQL supports defining data structures, manipulating data, declaring constraints, and retrieving data based on requirements.
• This chapter uses MySQL to create a database named "Student Attendance" and covers populating and querying it with SQL.

Installing MySQL

• MySQL is an open-source RDBMS software downloadable from https://dev.mysql.com/downloads.
• After installation, start the MySQL service, and the mysql> prompt indicates readiness for SQL statements.
• Rules for writing SQL statements in MySQL:
  • SQL is case-insensitive, treating "name" and "NAME" as the same.
  • SQL statements must end with a semicolon (;).
  • For multiline SQL statements, omit the semicolon after the first line, press Enter to continue, and the prompt changes from mysql> to ->.
  • Add a semicolon after the last line and press Enter to execute.

Data Types and Constraints in MySQL

• A database consists of one or more relations (tables), each made up of attributes (columns).
• Each attribute has a specific data type, and constraints can be applied to restrict attribute values.
• Data type indicates the type of data an attribute can hold and determines permissible operations (e.g., arithmetic operations on numeric data but not on character data).
• Common MySQL data types include:
  • CHAR(n): Fixed-length character data of length n (0 to 255). Declaring CHAR(10) reserves space for 10 characters, padding with spaces if fewer characters are used (e.g., 'city' occupies 4 characters with 6 spaces).
  • VARCHAR(n): Variable-length character data of length n (0 to 65535). Declaring VARCHAR(30) allows up to 30 characters, but only the actual string length is stored (e.g., 'city' occupies space for 4 characters).
  • Other types include numeric (e.g., INT, FLOAT), date and time (e.g., DATE), and string types.

SQL for Data Definition

CREATE DATABASE:

• Used to create a new database, e.g., CREATE DATABASE StudentAttendance; creates a database named "StudentAttendance".
• In Linux, database and table names are case-sensitive; in Windows, they are not.
• Best practice: Use consistent letter case for database and table names as used during creation.

USE Statement:

• Selects the database to work with, e.g., USE StudentAttendance; makes "StudentAttendance" the active database.
• A DBMS can manage multiple databases, so selecting the desired database is necessary.

SHOW TABLES:

• Lists all tables in the active database, e.g., SHOW TABLES; returns an empty set if no tables exist.

CREATE TABLE:

• Defines a table with attributes, data types, and constraints.
• Syntax: CREATE TABLE tablename (attribute1 datatype constraint, attribute2 datatype constraint, ...);
• Key points:
  • N represents the number of columns (degree of the relation).
  • Attribute names define column names.
  • Data types specify the type of data an attribute can hold.
  • Constraints restrict attribute values; by default, attributes can take NULL values except primary keys.
• Example for table STUDENT:
  • RollNumber: INT (3 digits, max 100 students).
  • SName: VARCHAR(20) (variable-length string, max 20 characters).
  • SDateofBirth: DATE.
  • GUID: CHAR(12) (fixed-length 12-digit Aadhaar number).
• SQL statement: CREATE TABLE STUDENT (RollNumber INT, SName VARCHAR(20), SDateofBirth DATE, GUID CHAR(12));

ALTER TABLE:

Modifies the structure of an existing table by adding, removing, or changing attributes and constraints.

• Add Primary Key:
  • Adds a primary key to a table, e.g., ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);.
  • For composite keys, specify multiple attributes, e.g., ALTER TABLE ATTENDANCE ADD PRIMARY KEY (AttendanceDate, RollNumber);.
• Add Foreign Key:
  • Adds a foreign key to reference a primary key in another table.
  • Requirements:
    • Referenced table must exist.
    • Referenced attribute must be part of the primary key.
    • Data types and sizes of referencing and referenced attributes must match.
  • Syntax: ALTER TABLE table_name ADD FOREIGN KEY (attribute) REFERENCES referenced_table (attribute);
  • Example: ALTER TABLE STUDENT ADD FOREIGN KEY (GUID) REFERENCES GUARDIAN (GUID);.
• Add UNIQUE Constraint:
  • Ensures no duplicate values in a column, e.g., ALTER TABLE GUARDIAN ADD UNIQUE (GPhone);.
• Add Attribute:
  • Adds a new column, e.g., ALTER TABLE GUARDIAN ADD income INT;.
• Modify Data Type:
  • Changes an attribute’s data type, e.g., ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);.
• Modify Constraint:
  • Changes an attribute’s constraint, e.g., ALTER TABLE STUDENT MODIFY SName VARCHAR(20) NOT NULL;.
  • Data type must be specified with the constraint.
• Add Default Value:
  • Sets a default value for an attribute, e.g., ALTER TABLE STUDENT MODIFY SDateofBirth DATE DEFAULT '2000-05-15';.
  • Data type must be specified.
• Remove Attribute:
  • Deletes a column, e.g., ALTER TABLE GUARDIAN DROP income;.
• Remove Primary Key:
  • Removes the primary key, e.g., ALTER TABLE GUARDIAN DROP PRIMARY KEY;.
  • A new primary key should be added to maintain uniqueness.

DROP Statement:

• Permanently removes a table or database.
• Syntax:
  • Drop table: DROP TABLE table_name;
  • Drop database: DROP DATABASE database_name;
• Cautions:
  • Dropping a database removes all its tables.
  • DROP is irreversible, so use with caution.

SQL for Data Manipulation

• Data manipulation involves inserting, retrieving, updating, or deleting data in a database.

INSERT INTO Statement:

• Inserts new records into a table.
• Syntax: INSERT INTO tablename VALUES (value1, value2, ...);
• Values correspond to attributes in order; attribute names are optional if all attributes are populated.
• Caution: Populate referenced tables (e.g., GUARDIAN) before tables with foreign keys (e.g., STUDENT).
• Example: INSERT INTO GUARDIAN VALUES ('444444444444', 'Amit Ahuja', '5711492685', 'G-35, Ashok Vihar, Delhi');

UPDATE Statement:

• Modifies existing records in a table.
• Syntax: UPDATE table_name SET column = value WHERE condition;
• The WHERE clause specifies which records to update.
• Example: UPDATE STUDENT SET GUID = '101010101010' WHERE RollNumber = 3; updates the GUID for the student with RollNumber 3.
• Caution: Omitting the WHERE clause updates all records, so use carefully.

DELETE Statement:

• Removes records from a table.
• Syntax: DELETE FROM table_name WHERE condition;
• Example: DELETE FROM STUDENT WHERE RollNumber = 2; deletes the record for the student with RollNumber 2.
• Caution: Omitting the WHERE clause deletes all records, so use carefully.

SQL for Data Query

SELECT Statement:

• Retrieves data from one or more tables.
• Syntax: SELECT * FROM table_name; displays all attributes of a table.
• Example: SELECT * FROM STUDENT; shows all records in the STUDENT table.

WHERE Clause:

• Filters records based on conditions.
• Used with SELECT, UPDATE, and DELETE statements.
• Example: SELECT * FROM STUDENT WHERE RollNumber = 3; retrieves the record for RollNumber 3.

DISTINCT Clause:

• Eliminates duplicate values in the result.
• Example: SELECT DISTINCT Stream FROM STUDENT; lists unique stream values.

BETWEEN Operator:

• Selects values within a range, inclusive of boundaries.
• Example: SELECT * FROM STUDENT WHERE RollNumber BETWEEN 1 AND 5; retrieves students with RollNumber 1 to 5.

IN Operator:

• Selects values matching any value in a list.
• Example: SELECT * FROM STUDENT WHERE Stream IN ('Science', 'Commerce'); retrieves students in Science or Commerce streams.

NULL Handling:

• Tests for NULL values using IS NULL or IS NOT NULL.
• Example: SELECT * FROM STUDENT WHERE GUID IS NULL; retrieves students with no GUID.

ORDER BY Clause:

• Sorts query results in ascending (default) or descending order.
• Example: SELECT * FROM STUDENT ORDER BY SName; sorts students by name in ascending order.
• Use ORDER BY SName DESC; for descending order.

LIKE Clause:

• Performs pattern matching with wildcard characters:
  • %: Represents zero or more characters.
  • _: Represents a single character.
• Example: SELECT * FROM STUDENT WHERE SName LIKE 'A%'; retrieves students with names starting with 'A'.

Data Updation and Deletion

UPDATE Statement:

• Modifies specific records based on conditions.
• Example: Updates GUID for a student, ensuring the WHERE clause targets the correct record.
• Ensures data integrity by updating only intended records.

DELETE Statement:

• Removes specific records based on conditions.
• Example: Deletes a student record when they leave the school.
• Ensures data accuracy by removing only intended records.

Importance of WHERE Clause:

• Critical in UPDATE and DELETE statements to avoid unintended changes to all records.
• Ensures precise data manipulation.