Worksheet: Introduction to Structured Query Language (SQL)

SQL is a standardized programming language used for managing and manipulating relational databases. It enables users to define data structures, insert, update, delete, and query data. Its features include simplicity, an English-like syntax, and portability across different database systems like MySQL and Oracle. SQL is significant as it allows for efficient data handling and retrieval in applications ranging from banking to web services.

A relational database comprises tables (relations) that store data in rows and columns. Each table has several attributes (columns) defining the data type and constraints. Relations refer to entire tables, while attributes specify the characteristics of data entries. For example, a 'Students' table (relation) might have attributes like 'RollNumber', 'Name', and 'DOB'.

Data Types in SQL define the type of data a column can hold. Commonly used data types in MySQL include: 1. INT: for integer values (e.g., 100). 2. VARCHAR(n): for variable-length strings up to 'n' characters (e.g., VARCHAR(20) for names). 3. CHAR(n): for fixed-length strings (e.g., CHAR(10) for codes). 4. DATE: for date values in 'YYYY-MM-DD' format (e.g., '2023-01-01'). 5. FLOAT: for floating-point numerical data (e.g., 10.5).

Primary keys uniquely identify each record in a table and ensure no duplicate entries (e.g., RollNumber in students table). Foreign keys create relationships between tables by referencing primary keys from another table (e.g., GUID in students referencing GUID in guardians). These constraints maintain data integrity and enforce relationship rules within a database.

To create a database, the SQL command is: CREATE DATABASE database_name; For example, CREATE DATABASE StudentAttendance; To create a table, the syntax is: CREATE TABLE table_name (attribute1 datatype constraint, ...); Example: CREATE TABLE Students (RollNumber INT PRIMARY KEY, SName VARCHAR(20) NOT NULL);

The SELECT statement retrieves data from tables. It can include WHERE clauses for filtering results (e.g., SELECT SName FROM Students WHERE RollNumber = 1; to get names of students with RollNumber 1). Additional clauses such as ORDER BY can organize results. Example: SELECT SName FROM Students ORDER BY SName ASC;

The INSERT statement adds new records to a table. The syntax is: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); For example, INSERT INTO Students (RollNumber, SName) VALUES (1, 'Atharv Ahuja'); adds a new student with RollNumber 1 and name 'Atharv Ahuja'.

The DELETE statement removes existing records from a table based on specified conditions. It must be used cautiously with the WHERE clause to prevent deleting all records (e.g., DELETE FROM Students WHERE RollNumber = 1; removes the entry with RollNumber 1). Omitting the WHERE clause would result in deleting all records from the table.

The ALTER TABLE statement is used to modify an existing table's structure, such as adding or dropping columns, or altering data types. For example, ALTER TABLE Students ADD DateOfBirth DATE; adds a new column for the students' birthdates. It can also be used to modify existing columns (ALTER TABLE Students MODIFY SName VARCHAR(30);).

Data integrity ensures the accuracy and consistency of data within a database. Constraints like PRIMARY KEY (ensures unique records), FOREIGN KEY (maintains relationships), and UNIQUE (guarantees distinct values in a column) help enforce data integrity. For example, having a PRIMARY KEY like RollNumber prevents duplicate student entries, ensuring accurate student identification.

SQL commands are structured into categories: DDL (Data Definition Language) includes commands like CREATE and ALTER for managing database schema, while DML (Data Manipulation Language) includes commands such as INSERT, UPDATE, DELETE, used to manage data within the schema. For example, to insert a new record: INSERT INTO table (columns) VALUES (values); This command enables users to add data effectively.

Data types define the nature of data that can be stored in a database column. Selecting appropriate data types can optimize storage and performance. For instance, using INT for numerical values ensures efficient storage and faster computations compared to using VARCHAR which consumes more space. Examples include: INT for whole numbers, VARCHAR for variable-length strings, and DATE for date entries.

Primary keys uniquely identify each record in a table and cannot contain NULL values. Foreign keys, on the other hand, establish a relationship between tables by referring to a primary key in another table. This relationship helps maintain referential integrity within the database. For example, in a STUDENT table, RollNumber can be a primary key, while GUID in GUARDIAN can serve as a foreign key referencing GUID in STUDENT.

Data retrieval in SQL is primarily handled by the SELECT statement, which specifies the desired columns and from which table to retrieve. The WHERE clause is crucial for filtering results based on specific conditions. For instance, SELECT SName FROM STUDENT WHERE RollNumber = 1 retrieves the name of the student with RollNumber 1, ensuring focused data access.

Managing NULL values is essential as they represent missing or unknown data. SQL provides IS NULL and IS NOT NULL conditions to handle these. For instance, when querying for students with no phone number, one can use SELECT * FROM STUDENT WHERE GPhone IS NULL. Ensuring correct handling of NULLs helps maintain data integrity across relations.

Constraints are rules that help maintain accuracy and integrity of data. Common types include NOT NULL, UNIQUE, CHECK, and FOREIGN KEY. For example, a NOT NULL constraint on a student's name ensures that no record can have a missing name, while a UNIQUE constraint on a student's email ensures no duplicates exist.

The UPDATE statement modifies existing records, while the DELETE statement removes records from the table. Both require caution, particularly the DELETE statement which can affect all records if not constrained by a proper WHERE clause. For example, UPDATE STUDENT SET SName='John' WHERE RollNumber=5 changes the name, while DELETE FROM STUDENT WHERE RollNumber=5 removes that record entirely.

The ORDER BY clause arranges the retrieved data in a specified order, either ascending or descending based on specified columns. For example, SELECT SName FROM STUDENT ORDER BY SName ASC retrieves all student names sorted alphabetically. Effective use of ORDER BY can enhance readability and analysis of data.

SQL functions like COUNT, SUM, and AVG perform aggregations that support data analysis. For example, COUNT(SName) returns the total number of student names, SUM(Score) calculates the total scores of students, and AVG(Salary) computes the average salary of employees. These functions are essential for deriving insights from large datasets.

Creating a new database involves the CREATE DATABASE command followed by selecting it with USE. For creating a table, the CREATE TABLE syntax specifies attributes, their data types, and constraints. For instance: CREATE TABLE STUDENT (RollNumber INT PRIMARY KEY, SName VARCHAR(20) NOT NULL); This establishes structure and ensures data integrity.

Investigate the consequences of data type mismatches and relate these to SQL performance and integrity. Use comparative examples to support your argument.

Analyze various normalization forms and their effects on database structures. Discuss trade-offs between normalization and performance.

Create a schema that includes at least three tables (e.g., Books, Authors, and Sales) with descriptions for their relationships and constraints.

Discuss scenarios where unintended data loss could occur and mention best practices for using these commands safely.

Demonstrate using aggregation functions (e.g., SUM) and WHERE clauses effectively while ensuring proper data grouping.

Provide an evaluative summary of constraints and their significance to database accuracy and relationships.

Discuss indexing, query plans, and how they affect the execution speed of SQL statements.

Analyze situations where NULLs may lead to data ambiguity or where their presence might be necessary.

Examine transaction logs, backups, or rollback features that SQL provides for restoring data.

Summarize security techniques such as parameterized queries and the use of ORM layers to prevent SQL injection.