Scribd
Upload
4 views5 pages

SQL Quick Reference Sheet

This document is a SQL quick reference sheet that includes commands for creating tables for customers and orders, along with sample data insertion, updates, and deletions. It also provides examples of SQL queries such as Cartesian products and simple aggregation. The content is adapted from various online resources and includes links for further practice.

Uploaded by

realme627673
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views5 pages

SQL Quick Reference Sheet

This document is a SQL quick reference sheet that includes commands for creating tables for customers and orders, along with sample data insertion, updates, and deletions. It also provides examples of SQL queries such as Cartesian products and simple aggregation. The content is adapted from various online resources and includes links for further practice.

Uploaded by

realme627673
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1

SQL Quick Reference Sheet


Adapted from [Link]
7354699741521203201-b7bN/ which takes from [Link]
Quick practice at [Link]

-- Create the customers table


CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT,
city VARCHAR(255),
country VARCHAR(255),
has_subscription BOOLEAN NOT NULL
);

-- Create the orders table


CREATE TABLE orders (
order_id INT PRIMARY KEY,
cus_id INT,
date DATE NOT NULL,
cost DECIMAL(10, 2) NOT NULL,
discount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
status VARCHAR(50) NOT NULL,
FOREIGN KEY (cus_id) REFERENCES customers(id)
);
2

-- Insert sample data into customers table


INSERT INTO customers (id, name, age, city, country, has_subscription) VALUES
(1, 'Adam', 58, 'New York', 'USA', TRUE),
(2, 'Bella', NULL, 'Tijuana', 'Mexico', FALSE),
(3, 'Chetan', 36, 'New Delhi', 'India', TRUE);

-- Insert sample data into orders table


INSERT INTO orders (order_id, cus_id, date, cost, discount, status) VALUES
(101, 1, '2023-04-05', 300.00, 0.00, 'Delivered'),
(102, 2, '2023-10-02', 400.00, 0.00, 'Shipped'),
(103, 2, '2024-11-19', 100.00, 25.35, 'TBD'),
(999, NULL, '2027-06-16', 1200.00, 0.00, 'TBD');

Insert Statement
INSERT INTO customers (id, name, age, city, country, has_subscription) VALUES
(4, 'Diana', 29, 'Berlin', 'Germany', TRUE),
(5, 'Eli', 42, 'Toronto', 'Canada', FALSE);

Update statement
- - Diana move to London
UPDATE customers
SET city = 'London', country = 'UK'
WHERE id = 4;

Delete statement
-- remove both the customer and their orders,
DELETE FROM orders WHERE cus_id = 5;
DELETE FROM customers WHERE id = 5;
3
4

Cartesian Product
Example
Show the list or order with the customer names

SELECT o.order_id, [Link]


FROM customers c, orders o
WHERE [Link] = o.cus_id;

Order By

Simple Aggregation
5

You might also like