SQL (Structured Query Language) is a domain-specific language used in programming and managing relational databases. It's designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). SQL allows users to query, manipulate, and manage data stored in a relational database.
Here's a basic breakdown of some common SQL commands with examples:
SELECT: Retrieves data from a database.
sqlSELECT * FROM employees;
This selects all columns from the "employees" table.
INSERT: Inserts new data into a database table.
sqlINSERT INTO employees (id, name, age) VALUES (1, 'John Doe', 30);
This inserts a new employee with an ID of 1, name 'John Doe', and age 30 into the "employees" table.
UPDATE: Modifies existing data in a database table.
sqlUPDATE employees SET age = 31 WHERE id = 1;
This updates the age of the employee with an ID of 1 to 31.
DELETE: Removes data from a database table.
sqlDELETE FROM employees WHERE id = 1;
This deletes the employee with an ID of 1 from the "employees" table.
CREATE TABLE: Creates a new table in the database.
sqlCREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
This creates a "customers" table with columns for ID, name, and email.
ALTER TABLE: Modifies an existing table structure.
sqlALTER TABLE customers ADD COLUMN phone VARCHAR(15);
This adds a new column "phone" to the "customers" table.
DROP TABLE: Deletes a table from the database.
sqlDROP TABLE customers;
This removes the "customers" table from the database.
These are just some of the basic SQL commands. SQL also supports more complex operations like joins, subqueries, and aggregate functions for advanced data manipulation and analysis.
sql-- Create the books table
CREATE TABLE books (
book_id INT PRIMARYKEY, title VARCHAR(100), author VARCHAR(100), genre VARCHAR(50), year_published INT ); -- Create the borrowers table
CREATE TABLEborrowers (
borrower_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);
-- Insert some sample data into the books table
INSERT INTO books (book_id, title, author, genre, year_published) VALUES
(1, 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 1960),
(2, '1984', 'George Orwell', 'Science Fiction', 1949),
(3, 'Pride and Prejudice', 'Jane Austen', 'Romance', 1813);
-- Insert some sample data into the borrowers table
INSERT INTO borrowers (borrower_id, name, email) VALUES
(1, 'Alice Johnson', 'alice@example.com'),
(2, 'Bob Smith', 'bob@example.com');
-- Query all books
SELECT * FROM books;
-- Query all borrowers
SELECT* FROM borrowers;
-- Update the genre of a book
UPDATE books SETgenre = 'Classic' WHERE title = '1984';
-- Query books published after 1950
SELECT * FROM books WHERE year_published > 1950;
In this example, we first create two tables: "books" and "borrowers". Then, we insert some sample data into these tables. After that, we perform some queries to retrieve information from the tables and update the genre of one book. Finally, we query books published after 1950.