Introduction to SQL

What is SQL?

• SQL stands for Structured Query Language.
• Used to work with databases, primarily relational databases.
• A database is a collection of tables.

Importance of SQL

• SQL is essential for accessing and manipulating data within databases.
• Without SQL, you cannot access or manipulate the data stored in the database.

Key Concepts of SQL

• Table Structure: Data in tables is organized in rows and columns.
• Database Management Tools: Examples include MySQL, SQL Server, Oracle, etc.
• SQL commands are used to perform operations on databases.

Types of SQL Operations

• CRUD Operations:
  • Create: Create databases and tables.
  • Read: Access data from tables.
  • Update: Modify existing data.
  • Delete: Remove data from tables.

Categories of SQL Commands

1. Data Definition Language (DDL): Commands for defining database structures (e.g., CREATE, ALTER, DROP).
2. Data Manipulation Language (DML): Commands for manipulating data (e.g., SELECT, INSERT, UPDATE, DELETE).
3. Data Control Language (DCL): Commands for controlling access to data (e.g., GRANT, REVOKE).
4. Transaction Control Language (TCL): Commands for managing transactions (e.g., COMMIT, ROLLBACK).

SQL Commands Overview

• Creating a Database: CREATE DATABASE database_name;
• Creating a Table: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);
• Inserting Data: INSERT INTO table_name (columns) VALUES (values);
• Selecting Data: SELECT * FROM table_name;
• Updating Data: UPDATE table_name SET column1 = value WHERE condition;
• Deleting Data: DELETE FROM table_name WHERE condition;*

Data Types in SQL

• Numeric: Integers, floats, etc.
• String: CHAR, VARCHAR, TEXT, etc.
• Date and Time: DATE, TIME, DATETIME, etc.

Creating a View

• A view is a virtual table created from an existing table or multiple tables.
• Syntax: CREATE VIEW view_name AS SELECT ... FROM table_name WHERE condition;
• Changes in the underlying table reflect in the view and vice-versa.

Example Queries

1. Finding Names of Sailors Who Reserved Boat Number 3: SELECT s.name FROM Sailors s, Reserves r WHERE s.sid = r.sid AND r.bid = 3;
2. Finding Names of Sailors Who Reserved the Red Boat: SELECT s.name FROM Sailors s, Reserves r, Boats b WHERE s.sid = r.sid AND r.bid = b.bid AND b.color = 'Red';
3. Finding Colors of Boats Reserved by Sandeep: SELECT b.color FROM Boats b, Reserves r, Sailors s WHERE b.bid = r.bid AND r.sid = s.sid AND s.name = 'Sandeep';
4. Finding Names of Sailors Who Have Reserved At Least One Boat: SELECT DISTINCT s.name FROM Sailors s, Reserves r WHERE s.sid = r.sid;

Conclusion

• SQL is a powerful language for managing and manipulating databases.
• Understanding SQL commands and their structure is essential for working with databases effectively.