SQL

 

The SQL CREATE DATABASE Statement

CREATE DATABASE databasename;

Click here for video analysis

https://youtu.be/i8qDrl2-_tk

The SQL DROP DATABASE Statement

DROP DATABASE databasename;

The SQL BACKUP DATABASE Statement

BACKUP DATABASE databasename
TO DISK = 'filepath';

The SQL CREATE TABLE Statement

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

The SQL DROP TABLE Statement

DROP TABLE table_name;

ALTER TABLE - ADD Column

ALTER TABLE Customers
ADD Email varchar(255);

ALTER TABLE Customers
DROP COLUMN Email;

To rename a column in a table in SQL Server, use the following syntax:

EXEC sp_rename 'table_name.old_name',  'new_name', 'COLUMN';

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Prevents actions that would destroy links between tables
• CHECK - Ensures that the values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column if no value is specified
• CREATE INDEX - Used to create and retrieve data from the database very quickly

SQL NOT NULL on CREATE TABLE

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

SQL UNIQUE Constraint on CREATE TABLE

CREATE TABLE Persons (
    ID int NOT NULL UNIQUE,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

SQL PRIMARY KEY on CREATE TABLE

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

SQL FOREIGN KEY on CREATE TABLE

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL CHECK on CREATE TABLE

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CHECK (Age>=18)
);

SQL DEFAULT on CREATE TABLE

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    City varchar(255) DEFAULT 'Sandnes'
);

CREATE INDEX Example

CREATE INDEX idx_lastname
ON Persons (LastName);

AUTO INCREMENT Field

CREATE TABLE Persons (
    Personid int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);

Now we want to select the records with an Order Date of "2008-11-11" from the table above.

We use the following SELECT statement:

SELECT * FROM Orders WHERE OrderDate='2008-11-11'

save this file as db.sql and import in your local database for fast execution

-- Run this script directly in the MySQL server query window it will automatically create the database and all the database objects.

DROP DATABASE IF EXISTS Company;

CREATE DATABASE Company;

-- Creating Company Schema

USE Company;

DROP TABLE IF EXISTS DEPARTMENT;

CREATE TABLE DEPARTMENT (

  dname        varchar(25) not null,

  dnumber      int not null,

  mgrssn      char(9) not null,

  mgrstartdate date,

  CONSTRAINT pk_Department primary key (dnumber),

  CONSTRAINT uk_dname UNIQUE (dname)

);

DROP TABLE IF EXISTS EMPLOYEE;

CREATE TABLE EMPLOYEE (

  fname    varchar(15) not null,

  minit    varchar(1),

  lname    varchar(15) not null,

  ssn     char(9),

  bdate    date,

  address  varchar(50),

  sex      char,

  salary   decimal(10,2),

  superssn char(9),

  dno      int,

  CONSTRAINT pk_employee primary key (ssn),

  #'--CONSTRAINT fk_employee_employee foreign key (superssn) references EMPLOYEE(ssn), -- Constraint Will be added later to prevent cyclic referential itegrity violation'

  CONSTRAINT fk_employee_department foreign key (dno) references DEPARTMENT(dnumber)

);

DROP TABLE IF EXISTS DEPENDENT;

CREATE TABLE DEPENDENT (

  essn           char(9),

  dependent_name varchar(15),

  sex            char,

  bdate          date,

  relationship   varchar(8),

  CONSTRAINT pk_essn_dependent_name primary key (essn,dependent_name),

  CONSTRAINT fk_dependent_employee foreign key (essn) references EMPLOYEE(ssn)

);

DROP TABLE IF EXISTS DEPT_LOCATIONS;

CREATE TABLE DEPT_LOCATIONS (

  dnumber   int,

  dlocation varchar(15),

  CONSTRAINT pk_dept_locations primary key (dnumber,dlocation),

  CONSTRAINT fk_deptlocations_department foreign key (dnumber) references DEPARTMENT(dnumber)

);

DROP TABLE IF EXISTS PROJECT;

CREATE TABLE PROJECT (

  pname      varchar(25) not null,

  pnumber    int,

  plocation  varchar(15),

  dnum       int not null,

  CONSTRAINT ok_project primary key (pnumber),

  CONSTRAINT uc_pnumber unique (pname),

  CONSTRAINT fk_project_department foreign key (dnum) references DEPARTMENT(dnumber)

);

DROP TABLE IF EXISTS WORKS_ON;

CREATE TABLE WORKS_ON (

  essn   char(9),

  pno    int,

  hours  decimal(4,1),

  CONSTRAINT pk_worksOn primary key (essn,pno),

  CONSTRAINT fk_workson_employee foreign key (essn) references EMPLOYEE(ssn),

  CONSTRAINT fk_workson_project foreign key (pno) references PROJECT(pnumber)

);

-- Insert all records

INSERT INTO DEPARTMENT VALUES ('Research','5','333445555','1978-05-22');

INSERT INTO DEPARTMENT VALUES ('Administration','4','987654321','1985-01-01');

INSERT INTO DEPARTMENT VALUES ('Headquarters','1','888665555','1971-06-19');

INSERT INTO DEPARTMENT VALUES ('Software','6','111111100','1999-05-15');

INSERT INTO DEPARTMENT VALUES ('Hardware','7','444444400','1998-05-15');

INSERT INTO DEPARTMENT VALUES ('Sales','8','555555500','1997-01-01');

INSERT INTO EMPLOYEE VALUES ('Bob','B','Bender','666666600','1968-04-17','8794 Garfield, Chicago, IL','M','96000.00',NULL,'8' );

INSERT INTO EMPLOYEE VALUES ('Kim','C','Grace','333333300','1970-10-23','6677 Mills Ave, Sacramento, CA','F','79000.00',NULL,'6');

INSERT INTO EMPLOYEE VALUES ('James','E','Borg','888665555','1927-11-10','450 Stone, Houston, TX','M','55000.00',NULL,'1');

INSERT INTO EMPLOYEE VALUES ('Alex','D','Freed','444444400','1950-10-09','4333 Pillsbury, Milwaukee, WI','M','89000.00',NULL,'7');

INSERT INTO EMPLOYEE VALUES ('Evan','E','Wallis','222222200','1958-01-16','134 Pelham, Milwaukee, WI','M','92000.00',NULL,'7');

INSERT INTO EMPLOYEE VALUES ('Jared','D','James','111111100','1966-10-10','123 Peachtree, Atlanta, GA','M','85000.00',NULL,'6');

INSERT INTO EMPLOYEE VALUES ('John','C','James','555555500','1975-06-30','7676 Bloomington, Sacramento, CA','M','81000.00',NULL,'6');

INSERT INTO EMPLOYEE VALUES ('Andy','C','Vile','222222202','1944-06-21','1967 Jordan, Milwaukee, WI','M','53000.00','222222200','7');

INSERT INTO EMPLOYEE VALUES ('Brad','C','Knight','111111103','1968-02-13','176 Main St., Atlanta, GA','M','44000.00','111111100','6');

INSERT INTO EMPLOYEE VALUES ('Josh','U','Zell','222222201','1954-05-22','266 McGrady, Milwaukee, WI','M','56000.00','222222200','7');

INSERT INTO EMPLOYEE VALUES ('Justin','n','Mark','111111102','1966-01-12','2342 May, Atlanta, GA','M','40000.00','111111100','6');

INSERT INTO EMPLOYEE VALUES ('Jon','C','Jones','111111101','1967-11-14','111 Allgood, Atlanta, GA','M','45000.00','111111100','6');

INSERT INTO EMPLOYEE VALUES ('Ahmad','V','Jabbar','987987987','1959-03-29','980 Dallas, Houston, TX','M','25000.00','987654321','4');

INSERT INTO EMPLOYEE VALUES ('Joyce','A','English','453453453','1962-07-31','5631 Rice, Houston, TX','F','25000.00','333445555','5');

INSERT INTO EMPLOYEE VALUES ('Ramesh','K','Narayan','666884444','1952-09-15','971 Fire Oak, Humble, TX','M','38000.00','333445555','5');

INSERT INTO EMPLOYEE VALUES ('Alicia','J','Zelaya','999887777','1958-07-19','3321 Castle, Spring, TX','F','25000.00','987654321','4');

INSERT INTO EMPLOYEE VALUES ('John','B','Smith','123456789','1955-01-09','731 Fondren, Houston, TX','M','30000.00','333445555','5');

INSERT INTO EMPLOYEE VALUES ('Jennifer','S','Wallace','987654321','1931-06-20','291 Berry, Bellaire, TX','F','43000.00','888665555','4');

INSERT INTO EMPLOYEE VALUES ('Franklin','T','Wong','333445555','1945-12-08','638 Voss, Houston, TX','M','40000.00','888665555','5');

INSERT INTO EMPLOYEE VALUES ('Tom','G','Brand','222222203','1966-12-16','112 Third St, Milwaukee, WI','M','62500.00','222222200','7');

INSERT INTO EMPLOYEE VALUES ('Jenny','F','Vos','222222204','1967-11-11','263 Mayberry, Milwaukee, WI','F','61000.00','222222201','7');

INSERT INTO EMPLOYEE VALUES ('Chris','A','Carter','222222205','1960-03-21','565 Jordan, Milwaukee, WI','F','43000.00','222222201','7');

INSERT INTO EMPLOYEE VALUES ('Jeff','H','Chase','333333301','1970-01-07','145 Bradbury, Sacramento, CA','M','44000.00','333333300','6');

INSERT INTO EMPLOYEE VALUES ('Bonnie','S','Bays','444444401','1956-06-19','111 Hollow, Milwaukee, WI','F','70000.00','444444400','7');

INSERT INTO EMPLOYEE VALUES ('Alec','C','Best','444444402','1966-06-18','233 Solid, Milwaukee, WI','M','60000.00','444444400','7');

INSERT INTO EMPLOYEE VALUES ('Sam','S','Snedden','444444403','1977-07-31','987 Windy St, Milwaukee, WI','M','48000.00','444444400','7');

INSERT INTO EMPLOYEE VALUES ('Nandita','K','Ball','555555501','1969-04-16','222 Howard, Sacramento, CA','M','62000.00','555555500','6');

INSERT INTO EMPLOYEE VALUES ('Jill','J','Jarvis','666666601','1966-01-14','6234 Lincoln, Chicago, IL','F','36000.00','666666600','8');

INSERT INTO EMPLOYEE VALUES ('Kate','W','King','666666602','1966-04-16','1976 Boone Trace, Chicago, IL','F','44000.00','666666600','8');

INSERT INTO EMPLOYEE VALUES ('Lyle','G','Leslie','666666603','1963-06-09','417 Hancock Ave, Chicago, IL','M','41000.00','666666601','8');

INSERT INTO EMPLOYEE VALUES ('Billie','J','King','666666604','1960-01-01','556 Washington, Chicago, IL','F','38000.00','666666603','8');

INSERT INTO EMPLOYEE VALUES ('Jon','A','Kramer','666666605','1964-08-22','1988 Windy Creek, Seattle, WA','M','41500.00','666666603','8');

INSERT INTO EMPLOYEE VALUES ('Ray','H','King','666666606','1949-08-16','213 Delk Road, Seattle, WA','M','44500.00','666666604','8');

INSERT INTO EMPLOYEE VALUES ('Gerald','D','Small','666666607','1962-05-15','122 Ball Street, Dallas, TX','M','29000.00','666666602','8');

INSERT INTO EMPLOYEE VALUES ('Arnold','A','Head','666666608','1967-05-19','233 Spring St, Dallas, TX','M','33000.00','666666602','8');

INSERT INTO EMPLOYEE VALUES ('Helga','C','Pataki','666666609','1969-03-11','101 Holyoke St, Dallas, TX','F','32000.00','666666602','8');

INSERT INTO EMPLOYEE VALUES ('Naveen','B','Drew','666666610','1970-05-23','198 Elm St, Philadelphia, PA','M','34000.00','666666607','8');

INSERT INTO EMPLOYEE VALUES ('Carl','E','Reedy','666666611','1977-06-21','213 Ball St, Philadelphia, PA','M','32000.00','666666610','8');

INSERT INTO EMPLOYEE VALUES ('Sammy','G','Hall','666666612','1970-01-11','433 Main Street, Miami, FL','M','37000.00','666666611','8');

INSERT INTO EMPLOYEE VALUES ('Red','A','Bacher','666666613','1980-05-21','196 Elm Street, Miami, FL','M','33500.00','666666612','8');

INSERT INTO DEPENDENT VALUES ('333445555','Alice','F','1976-04-05','Daughter');

INSERT INTO DEPENDENT VALUES ('333445555','Theodore','M','1973-10-25','Son');

INSERT INTO DEPENDENT VALUES ('333445555','Joy','F','1948-05-03','Spouse');

INSERT INTO DEPENDENT VALUES ('987654321','Abner','M','1932-02-29','Spouse');

INSERT INTO DEPENDENT VALUES ('123456789','Michael','M','1978-01-01','Son');

INSERT INTO DEPENDENT VALUES ('123456789','Alice','F','1978-12-31','Daughter');

INSERT INTO DEPENDENT VALUES ('123456789','Elizabeth','F',Null,'Spouse'); -- Converted to Null as '0000-00-00' is an invalid date in SQL SERVER

INSERT INTO DEPENDENT VALUES ('444444400','Johnny','M','1997-04-04','Son');

INSERT INTO DEPENDENT VALUES ('444444400','Tommy','M','1999-06-07','Son');

INSERT INTO DEPENDENT VALUES ('444444401','Chris','M','1969-04-19','Spouse');

INSERT INTO DEPENDENT VALUES ('444444402','Sam','M','1964-02-14','Spouse');  

INSERT INTO DEPT_LOCATIONS VALUES ('1','Houston');

INSERT INTO DEPT_LOCATIONS VALUES ('4','Stafford');

INSERT INTO DEPT_LOCATIONS VALUES ('5','Bellaire');

INSERT INTO DEPT_LOCATIONS VALUES ('5','Houston');

INSERT INTO DEPT_LOCATIONS VALUES ('5','Sugarland');

INSERT INTO DEPT_LOCATIONS VALUES ('6','Atlanta');

INSERT INTO DEPT_LOCATIONS VALUES ('6','Sacramento');

INSERT INTO DEPT_LOCATIONS VALUES ('7','Milwaukee');

INSERT INTO DEPT_LOCATIONS VALUES ('8','Chicago');

INSERT INTO DEPT_LOCATIONS VALUES ('8','Dallas');

INSERT INTO DEPT_LOCATIONS VALUES ('8','Miami');

INSERT INTO DEPT_LOCATIONS VALUES ('8','Philadephia');

INSERT INTO DEPT_LOCATIONS VALUES ('8','Seattle');

INSERT INTO PROJECT VALUES ('ProductX','1','Bellaire','5');

INSERT INTO PROJECT VALUES ('ProductY','2','Sugarland','5');

INSERT INTO PROJECT VALUES ('ProductZ','3','Houston','5');

INSERT INTO PROJECT VALUES ('Computerization','10','Stafford','4');

INSERT INTO PROJECT VALUES ('Reorganization','20','Houston','1');

INSERT INTO PROJECT VALUES ('Newbenefits','30','Stafford','4');

INSERT INTO PROJECT VALUES ('OperatingSystems','61','Jacksonville','6');

INSERT INTO PROJECT VALUES ('DatabaseSystems','62','Birmingham','6');

INSERT INTO PROJECT VALUES ('Middleware','63','Jackson','6');

INSERT INTO PROJECT VALUES ('InkjetPrinters','91','Phoenix','7');

INSERT INTO PROJECT VALUES ('LaserPrinters','92','LasVegas','7');

INSERT INTO WORKS_ON VALUES ('123456789','1','32.5');

INSERT INTO WORKS_ON VALUES ('123456789','2','7.5');

INSERT INTO WORKS_ON VALUES ('666884444','3','40.0');

INSERT INTO WORKS_ON VALUES ('453453453','1','20.0');

INSERT INTO WORKS_ON VALUES ('453453453','2','20.0');

INSERT INTO WORKS_ON VALUES ('333445555','2','10.0');

INSERT INTO WORKS_ON VALUES ('333445555','3','10.0');

INSERT INTO WORKS_ON VALUES ('333445555','10','10.0');

INSERT INTO WORKS_ON VALUES ('333445555','20','10.0');

INSERT INTO WORKS_ON VALUES ('999887777','30','30.0');

INSERT INTO WORKS_ON VALUES ('999887777','10','10.0');

INSERT INTO WORKS_ON VALUES ('987987987','10','35.0');

INSERT INTO WORKS_ON VALUES ('987987987','30','5.0');

INSERT INTO WORKS_ON VALUES ('987654321','30','20.0');

INSERT INTO WORKS_ON VALUES ('987654321','20','15.0');

INSERT INTO WORKS_ON VALUES ('888665555','20','0.0');

INSERT INTO WORKS_ON VALUES ('111111100','61','40.0');

INSERT INTO WORKS_ON VALUES ('111111101','61','40.0');

INSERT INTO WORKS_ON VALUES ('111111102','61','40.0');

INSERT INTO WORKS_ON VALUES ('111111103','61','40.0');

INSERT INTO WORKS_ON VALUES ('222222200','62','40.0');

INSERT INTO WORKS_ON VALUES ('222222201','62','48.0');

INSERT INTO WORKS_ON VALUES ('222222202','62','40.0');

INSERT INTO WORKS_ON VALUES ('222222203','62','40.0');

INSERT INTO WORKS_ON VALUES ('222222204','62','40.0');

INSERT INTO WORKS_ON VALUES ('222222205','62','40.0');

INSERT INTO WORKS_ON VALUES ('333333300','63','40.0');

INSERT INTO WORKS_ON VALUES ('333333301','63','46.0');

INSERT INTO WORKS_ON VALUES ('444444400','91','40.0');

INSERT INTO WORKS_ON VALUES ('444444401','91','40.0');

INSERT INTO WORKS_ON VALUES ('444444402','91','40.0');

INSERT INTO WORKS_ON VALUES ('444444403','91','40.0');

INSERT INTO WORKS_ON VALUES ('555555500','92','40.0');

INSERT INTO WORKS_ON VALUES ('555555501','92','44.0');

INSERT INTO WORKS_ON VALUES ('666666601','91','40.0');

INSERT INTO WORKS_ON VALUES ('666666603','91','40.0');

INSERT INTO WORKS_ON VALUES ('666666604','91','40.0');

INSERT INTO WORKS_ON VALUES ('666666605','92','40.0');

INSERT INTO WORKS_ON VALUES ('666666606','91','40.0');

INSERT INTO WORKS_ON VALUES ('666666607','61','40.0');

INSERT INTO WORKS_ON VALUES ('666666608','62','40.0');

INSERT INTO WORKS_ON VALUES ('666666609','63','40.0');

INSERT INTO WORKS_ON VALUES ('666666610','61','40.0');

INSERT INTO WORKS_ON VALUES ('666666611','61','40.0');

INSERT INTO WORKS_ON VALUES ('666666612','61','40.0');

INSERT INTO WORKS_ON VALUES ('666666613','61','30.0');

INSERT INTO WORKS_ON VALUES ('666666613','62','10.0');

INSERT INTO WORKS_ON VALUES ('666666613','63','10.0');

-- Adding FK constraint after loading data into system

Alter table EMPLOYEE

ADD CONSTRAINT fk_employee_employee foreign key (superssn) references EMPLOYEE(ssn);

-- Select Staements to validate all the tables were created properly

SELECT * FROM DEPARTMENT;

SELECT * FROM DEPENDENT;

SELECT * FROM DEPT_LOCATIONS;

SELECT * FROM EMPLOYEE;

SELECT * FROM PROJECT;

SELECT * FROM WORKS_ON;

Basic Queries

1. Retrieve all employees

   
   SELECT * FROM EMPLOYEE;
   

2. List all departments

   
   SELECT * FROM DEPARTMENT;
   

3. Find the names of all employees in a specific department (e.g., 'HR')

   
   SELECT fname, lname 
   FROM EMPLOYEE 
   WHERE dno = (SELECT dnumber FROM DEPARTMENT WHERE dname = 'HR');
   

4. Get the total number of employees in each department

   
   SELECT dno, COUNT(*) AS total_employees 
   FROM EMPLOYEE 
   GROUP BY dno;
   

Intermediate Queries

5. List department names along with their managers' names

   
   SELECT d.dname, e.fname, e.lname 
   FROM DEPARTMENT d 
   JOIN EMPLOYEE e ON d.mgrssn = e.ssn;
   

6. Get the highest-paid employee in each department

   
   SELECT e.fname, e.lname, e.salary, d.dname 
   FROM EMPLOYEE e 
   JOIN DEPARTMENT d ON e.dno = d.dnumber 
   WHERE e.salary = (SELECT MAX(salary) FROM EMPLOYEE WHERE dno = d.dnumber);
   

Advanced Queries

8. Find employees who earn more than their department’s average salary

   
   SELECT e.fname, e.lname, e.salary, d.dname 
   FROM EMPLOYEE e 
   JOIN DEPARTMENT d ON e.dno = d.dnumber 
   WHERE e.salary > (SELECT AVG(salary) FROM EMPLOYEE WHERE dno = e.dno);

9. Find departments with more than 5 employees

SELECT d.dname, COUNT(e.ssn) AS num_employees 
FROM DEPARTMENT d 
JOIN EMPLOYEE e ON d.dnumber = e.dno 
GROUP BY d.dname 
HAVING COUNT(e.ssn) > 5;

50 advanced queries from this table

-- Insert Queries

INSERT INTO DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate) VALUES ('Finance', 2, '987654321', '2019-05-15');

INSERT INTO EMPLOYEE (fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno) VALUES ('John', 'A', 'Doe', '111223333', '1985-06-25', '123 Main St', 'M', 60000.00, NULL, 1);

INSERT INTO EMPLOYEE (fname, minit, lname, ssn, bdate, address, sex, salary, superssn, dno) VALUES ('Jane', 'B', 'Smith', '444556666', '1990-07-15', '456 Elm St', 'F', 70000.00, '111223333', 2);

-- Select Queries

SELECT * FROM DEPARTMENT;

SELECT * FROM EMPLOYEE;

SELECT * FROM DEPENDENT;

SELECT fname, lname FROM EMPLOYEE WHERE salary > 65000;

SELECT COUNT(*) FROM EMPLOYEE WHERE dno = 1;

SELECT e.fname, e.lname, d.dname FROM EMPLOYEE e JOIN DEPARTMENT d ON e.dno = d.dnumber;

SELECT dname FROM DEPARTMENT WHERE dnumber IN (SELECT dno FROM EMPLOYEE WHERE salary > 60000);

SELECT AVG(salary) FROM EMPLOYEE;

SELECT e.fname, e.lname FROM EMPLOYEE e WHERE NOT EXISTS (SELECT * FROM DEPENDENT d WHERE ssn = ssn);

SELECT dname, COUNT(*) FROM EMPLOYEE e JOIN DEPARTMENT d ON dno = dnumber GROUP BY dname;

-- Update Queries

UPDATE EMPLOYEE SET salary = salary * 1.1 WHERE dno = 1;

UPDATE EMPLOYEE SET address = '789 Oak St' WHERE ssn = '444556666';

UPDATE DEPARTMENT SET mgrssn = '444556666' WHERE dnumber = 1;

UPDATE DEPENDENT SET relationship = 'Son' WHERE dependent_name = 'Alice Doe';

-- Delete Queries

DELETE FROM DEPENDENT WHERE ssn = '111223333';

DELETE FROM EMPLOYEE WHERE ssn = '111223333';

DELETE FROM DEPARTMENT WHERE dnumber = 2;

-- Aggregate Functions

SELECT dno, AVG(salary) FROM EMPLOYEE GROUP BY dno;

SELECT dno, MAX(salary) FROM EMPLOYEE GROUP BY dno;

SELECT dno, MIN(salary) FROM EMPLOYEE GROUP BY dno;

SELECT COUNT(*) FROM EMPLOYEE;

SELECT dno, COUNT(*) FROM EMPLOYEE GROUP BY dno;

-- Advanced Queries

SELECT e.fname, e.lname FROM EMPLOYEE e WHERE e.salary = (SELECT MAX(salary) FROM EMPLOYEE);

SELECT e.fname, e.lname FROM EMPLOYEE e WHERE e.salary > (SELECT AVG(salary) FROM EMPLOYEE);

SELECT dname FROM DEPARTMENT WHERE dnumber NOT IN (SELECT DISTINCT dno FROM EMPLOYEE);

-- Subqueries

SELECT fname, lname FROM EMPLOYEE WHERE dno = (SELECT dnumber FROM DEPARTMENT WHERE dname = 'Finance');

SELECT * FROM EMPLOYEE WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE);

SELECT fname, lname FROM EMPLOYEE WHERE dno IN (SELECT dnumber FROM DEPARTMENT WHERE mgrssn = '123456789');

-- Complex Joins

SELECT e.fname, e.lname, d.dname FROM EMPLOYEE e INNER JOIN DEPARTMENT d ON e.dno = d.dnumber;

SELECT e.fname, e.lname, dep.dependent_name FROM EMPLOYEE e LEFT JOIN DEPENDENT dep ON e.ssn = dep.ssn;

SELECT d.dname, COUNT(e.ssn) FROM DEPARTMENT d LEFT JOIN EMPLOYEE e ON d.dnumber = e.dno GROUP BY d.dname;

-- Nested Queries

SELECT fname, lname FROM EMPLOYEE WHERE salary > (SELECT AVG(salary) FROM EMPLOYEE WHERE dno = 1);

-- EXISTS and NOT EXISTS

SELECT fname, lname FROM EMPLOYEE e WHERE EXISTS (SELECT * FROM DEPENDENT d WHERE e.ssn = d.ssn);

SELECT fname, lname FROM EMPLOYEE e WHERE NOT EXISTS (SELECT * FROM DEPENDENT d WHERE e.ssn = d.ssn);

-- Set Operations

SELECT fname, lname FROM EMPLOYEE WHERE dno = 1 UNION SELECT fname, lname FROM EMPLOYEE WHERE dno = 2;

SELECT fname, lname FROM EMPLOYEE WHERE dno = 1 INTERSECT SELECT fname, lname FROM EMPLOYEE WHERE salary > 60000;

SELECT fname, lname FROM EMPLOYEE WHERE dno = 1 EXCEPT SELECT fname, lname FROM EMPLOYEE WHERE salary > 60000;

Amazon orders queries

-- Create the AmazonOrders table

CREATE TABLE AmazonOrders (

    OrderID INT PRIMARY KEY,

    CustomerName VARCHAR(255),

    ProductName VARCHAR(255),

    Category VARCHAR(100),

    Quantity INT,

    Price DECIMAL(10,2),

    OrderDate DATE,

    Status VARCHAR(50),

    PaymentMethod VARCHAR(50)

);

-- Insert data into the AmazonOrders table

INSERT INTO AmazonOrders (OrderID, CustomerName, ProductName, Category, Quantity, Price, OrderDate, Status, PaymentMethod) VALUES

(101, 'John Doe', 'iPhone 15', 'Electronics', 1, 999.99, '2024-03-20', 'Shipped', 'Credit Card'),

(102, 'Alice Smith', 'AirPods Pro', 'Electronics', 2, 199.99, '2024-03-22', 'Delivered', 'PayPal'),

(103, 'Bob Brown', 'Adidas Sneakers', 'Fashion', 1, 129.99, '2024-03-21', 'Pending', 'Debit Card'),

(104, 'Sarah Lee', 'Samsung Galaxy S24', 'Electronics', 1, 899.99, '2024-03-23', 'Delivered', 'Credit Card'),

(105, 'Mark Evans', 'Coffee Maker', 'Home', 1, 49.99, '2024-03-24', 'Cancelled', 'PayPal');

Common SQL Queries

1. Retrieve all orders

SELECT * FROM AmazonOrders;

2. Find all orders that are pending

SELECT * FROM AmazonOrders

WHERE Status = 'Pending';

3. Get total revenue from delivered orders

SELECT SUM(Quantity * Price) AS TotalRevenue

FROM AmazonOrders

WHERE Status = 'Delivered';

4. Count the number of orders by payment method

SELECT PaymentMethod, COUNT(*) AS OrderCount

FROM AmazonOrders

GROUP BY PaymentMethod;

5. List all orders sorted by OrderDate (most recent first)

SELECT * FROM AmazonOrders

ORDER BY OrderDate DESC;

6. Find customers who ordered electronics

SELECT DISTINCT CustomerName

FROM AmazonOrders

WHERE Category = 'Electronics';

7. Get the most expensive product ordered

SELECT * FROM AmazonOrders

ORDER BY Price DESC

LIMIT 1;

8. Find the total number of products sold per category

SELECT Category, SUM(Quantity) AS TotalSold

FROM AmazonOrders

GROUP BY Category;

9. Find orders placed in the last 7 days

SELECT * FROM AmazonOrders

WHERE OrderDate >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

10. Get the total number of orders by status

SELECT Status, COUNT(*) AS OrderCount

FROM AmazonOrders

GROUP BY Status;

Application form code(Html, Css, Sql, PHP)

Code 1: db.sql

CREATE DATABASE btech_admissions;

USE btech_admissions;

CREATE TABLE applications (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    email VARCHAR(100),

    phone VARCHAR(15),

    branch VARCHAR(50),

    document_path VARCHAR(255),

    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

Code 2: form.html

<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="UTF-8">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>BTech Application Form</title>

    <link rel="stylesheet" href="style.css">

</head>

<body>

    <div class="container">

        <h2>BTech Application Form</h2>

        <form action="submit.php" method="POST" enctype="multipart/form-data">

            <label for="name">Full Name:</label>

            <input type="text" id="name" name="name" required>

           

            <label for="email">Email:</label>

            <input type="email" id="email" name="email" required>

            <label for="phone">Phone:</label>

            <input type="tel" id="phone" name="phone" required>

            <label for="branch">Preferred Branch:</label>

            <select id="branch" name="branch" required>

                <option value="Computer Science">Computer Science</option>

                <option value="Mechanical">Mechanical</option>

                <option value="Civil">Civil</option>

                <option value="Electronics">Electronics</option>

            </select>

            <label for="document">Upload Documents (PDF only):</label>

            <input type="file" id="document" name="document" accept=".pdf" required>

            <button type="submit">Submit Application</button>

        </form>

    </div>

</body>

</html>

Code 3: submit.php

<?php

$servername = "localhost";

$username = "ams1";

$password = "Ams@1234";

$dbname = "btech_admissions";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

// Check if form is submitted

if ($_SERVER["REQUEST_METHOD"] == "POST") {

    $name = $_POST['name'];

    $email = $_POST['email'];

    $phone = $_POST['phone'];

    $branch = $_POST['branch'];

    // Handle file upload

    $target_dir = "uploads/";

    $file_name = basename($_FILES["document"]["name"]);

    $target_file = $target_dir . time() . "_" . $file_name;

    $file_type = strtolower(pathinfo($target_file, PATHINFO_EXTENSION));

    if ($file_type != "pdf") {

        die("Only PDF files are allowed.");

    }

    if (move_uploaded_file($_FILES["document"]["tmp_name"], $target_file)) {

        $sql = "INSERT INTO applications (name, email, phone, branch, document_path)

                VALUES ('$name', '$email', '$phone', '$branch', '$target_file')";

        if ($conn->query($sql) === TRUE) {

            echo "Application submitted successfully!";

        } else {

            echo "Error: " . $conn->error;

        }

    } else {

        echo "File upload failed.";

    }

}

$conn->close();

?>

Code 4: styel.css

body {

    font-family: Arial, sans-serif;

    background-color: #f4f4f4;

    display: flex;

    justify-content: center;

    align-items: center;

    height: 100vh;

}

.container {

    background: white;

    padding: 20px;

    border-radius: 10px;

    box-shadow: 0px 0px 10px 0px #aaa;

    width: 350px;

}

h2 {

    text-align: center;

}

form {

    display: flex;

    flex-direction: column;

}

label {

    margin-top: 10px;

}

input, select, button {

    padding: 8px;

    margin-top: 5px;

    border: 1px solid #ccc;

    border-radius: 5px;

}

button {

    background-color: #28a745;

    color: white;

    border: none;

    cursor: pointer;

    margin-top: 15px;

}

button:hover {

    background-color: #218838;

}

Click here for video analysis: https://youtu.be/sdJWjr4VjRg

In SQL, keys are constraints that help maintain data integrity and define relationships between tables. Here are the main types of keys in SQL:

1. Primary Key (PK)

• Uniquely identifies each record in a table.

• Cannot contain NULL values.

• Each table can have only one primary key.

• Example:

  
  CREATE TABLE Students (
      StudentID INT PRIMARY KEY,
      Name VARCHAR(50),
      Age INT
  );
  

2. Foreign Key (FK)

• Establishes a relationship between two tables.

• References the primary key of another table.

• Ensures referential integrity (i.e., prevents invalid data entry).

• Example:

  
  CREATE TABLE Orders (
      OrderID INT PRIMARY KEY,
      StudentID INT,
      FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
  );
  

3. Unique Key

• Ensures all values in the column(s) are unique.

• Unlike primary keys, a table can have multiple unique keys.

• Can contain NULL values (but only one NULL per column).

• Example:

  
  CREATE TABLE Employees (
      EmployeeID INT PRIMARY KEY,
      Email VARCHAR(100) UNIQUE
  );
  

4. Composite Key

• A key made up of two or more columns.

• Used when a single column is insufficient to uniquely identify records.

• Example:

  
  CREATE TABLE Enrollments (
      StudentID INT,
      CourseID INT,
      PRIMARY KEY (StudentID, CourseID)
  );
  

5. Candidate Key

• A column (or set of columns) that can uniquely identify a row.

• A table can have multiple candidate keys, but one is chosen as the primary key.

• Example:

  
  Candidate Keys: {EmployeeID, Email}
  Primary Key: EmployeeID
  

6. Super Key

• A set of one or more columns that uniquely identify a row.

• Includes primary keys, candidate keys, and any superset of them.

• Example:

  
  Super Keys: {EmployeeID}, {EmployeeID, Email}, {EmployeeID, Name}
  

7. Alternate Key

• A candidate key that is not chosen as the primary key.

• Example:

  
  Candidate Keys: {EmployeeID, Email}
  Primary Key: EmployeeID
  Alternate Key: Email
  

 Below are some SQL tables with different types of keys and queries to demonstrate their use.

1. Primary Key Example

Table: Students

CREATE TABLE Students (
    StudentID INT PRIMARY KEY, -- Primary Key
    Name VARCHAR(50),
    Age INT
);

📌 Query to insert data:

INSERT INTO Students (StudentID, Name, Age) 
VALUES (1, 'Alice', 22), (2, 'Bob', 24);

2. Foreign Key Example

Table: Courses

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(100)
);

Table: Enrollments (Foreign Key referencing Students)

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

📌 Query to insert data:

INSERT INTO Courses (CourseID, CourseName) 
VALUES (101, 'Mathematics'), (102, 'Physics');

INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID) 
VALUES (1, 1, 101), (2, 2, 102);

3. Unique Key Example

Table: Employees

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE, -- Unique Key
    Name VARCHAR(50)
);

📌 Query to insert data:

INSERT INTO Employees (EmployeeID, Email, Name) 
VALUES (1, 'alice@example.com', 'Alice'), 
       (2, 'bob@example.com', 'Bob');

4. Composite Key Example

Table: Orders (Composite Primary Key)

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID) -- Composite Key
);

📌 Query to insert data:

INSERT INTO Orders (OrderID, ProductID, Quantity) 
VALUES (1, 101, 2), (1, 102, 1);

5. Candidate Key Example

Table: Users

CREATE TABLE Users (
    UserID INT PRIMARY KEY, -- Chosen as Primary Key
    Email VARCHAR(100) UNIQUE, -- Candidate Key
    Username VARCHAR(50) UNIQUE -- Candidate Key
);

📌 Query to insert data:

INSERT INTO Users (UserID, Email, Username) 
VALUES (1, 'john@example.com', 'john123'), 
       (2, 'jane@example.com', 'jane_doe');

6. Super Key Example

• Super keys in Users table: {UserID}, {UserID, Email}, {UserID, Username}, {UserID, Email, Username}

• Since UserID is unique, it's the simplest Super Key.

CREATE TABLE account (

    ACCOUNT_ID INT PRIMARY KEY,

    PRODUCT_CD VARCHAR(10),

    OPEN_EMP_ID INT,

    AVAIL_BALANCE DECIMAL(10,2)

);

INSERT INTO account (ACCOUNT_ID, PRODUCT_CD, OPEN_EMP_ID, AVAIL_BALANCE) 

VALUES 

    (1, 'CHK', 101, 500.50),

    (2, 'SAV', 102, 1200.75),

    (3, 'CHK', 101, 800.25),

    (4, 'SAV', 103, 450.00),

    (5, 'CHK', 102, 300.00),

    (6, 'SAV', 101, 900.50),

    (7, 'SAV', 102, 650.80),

    (8, 'CHK', 101, 400.20);

SELECT 

    PRODUCT_CD,

    OPEN_EMP_ID,

    COUNT(DISTINCT ACCOUNT_ID) AS number_of_accounts,

    ROUND(SUM(AVAIL_BALANCE), 2) AS total_account,

    ROUND(AVG(AVAIL_BALANCE), 2) AS average_balance

FROM account

WHERE PRODUCT_CD IN ('CHK', 'SAV')

GROUP BY 1, 2

HAVING SUM(AVAIL_BALANCE) > 700

ORDER BY 1, 2 DESC;

Explanation of the Query

1. Filters rows where PRODUCT_CD is 'CHK' or 'SAV'.

2. Groups the results by PRODUCT_CD and OPEN_EMP_ID.

3. Counts distinct ACCOUNT_IDs per group (number_of_accounts).

4. Calculates the total available balance per group (total_account).

5. Finds the average balance per group (average_balance).

6. Filters only groups where SUM(AVAIL_BALANCE) > 700.

7. Sorts the results first by PRODUCT_CD, then by OPEN_EMP_ID in descending order.

🔐 How to Set a Secure SECRET_KEY?

1️⃣ Use a Random String (Not Recommended for Production)

SECRET_KEY = 'your_secret_key_here'

2️⃣ Generate a Secure Key (Recommended for Production)
Run this in Python to generate a strong key:

import secrets
print(secrets.token_hex(32))

Then, update config.py:

SECRET_KEY = 'your_generated_secret_key'

3️⃣ Use Environment Variables (Best for Deployment)