Final Home Work SQL for Alan Fayed

homework.sql

-- Explain the concepts of Entity and Attribute
-- In the relational data model, an entity is represented as a record in an entity set. In the relational data model, a field represents an attribute.
-- ref : https://binaryterms.com/difference-between-entity-and-attribute-in-database.html#:~:text=An%20entity%20is%20a%20distinguishable,a%20field%20represents%20an%20attribute.

-- Give examples representing multivalued and derived attribute
-- multivalued attribute : An attribute that can have multiple values for an entity. for Example User entity in an ecommerce have attributes email, password, phone, and address. The address attributes may have multiple value, because a user may have multiple address.
-- derived attribute : An attribute that can be derived from another attribute, for Example age attribute can be delivered from birthdate attribute.
-- ref : https://pctechnicalpro.blogspot.com/2017/04/types-of-attributes-in-dbms-with-example.html

-- CREATING EMPLOYEES :
    CREATE TABLE employees (
       employeeID INT AUTO_INCREMENT NOT NULL,
       first_name VARCHAR(100) NOT NULL,
       last_name VARCHAR(100) NOT NULL,
       birthday DATE NOT NULL,
       gender ENUM('M', 'F') NOT NULL,
       salary INT NOT NULL,
       branch_id INT NOT NULL,
       PRIMARY KEY (employeeID)
    );
    
--  Find Highest Salary from all employees
    SELECT MAX(salary) AS highest_salary FROM employees;
    
--  Find Highest salary from female employees
    SELECT MAX(salary) AS highest_salary FROM employees WHERE gender="F";
    
--  SQL to display the branch_id, number of employees and maximum salary of each branch
    SELECT branch_id, COUNT(employeeID) AS total_employees, MAX(salary) AS maximum_salary FROM employees GROUP BY branch_id;
    
-- change table name from to employees to newEmployees
    ρ employees (newEmployees)
    
-- display employees who birth before 1th January 1972
    σ birth_date < "1972-01-01" (newEmployees)
    
-- answer of s πfirst_name,last_name(σsalary>=70000)

-- what is sql joins ? 
-- join in sql is to combine rows from two or more tables, based on related column between them.
-- ref : https://www.w3schools.com/sql/sql_join.asp

-- How many types of joins are there in SQL
-- there are 4 types of joins in SQL, there are :
-- 1. Inner Join (Inner join returns rows from two or more fully qualified tables).
-- 2. Left Join (will return all rows from the table on the left that are ON and only rows from the table on the right that satisfy the join condition.)
-- 3. Right Join (will return all rows from the right-hand table that are ON with data from the left-hand table that satisfy the join condition)
-- 4. Full Join (will return all rows from both tables that are subject to ON including data that is NULL.)
-- ref : https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins
-- ref : https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/

-- Difference between Where and Having clause
-- 1. Where : WHERE clause is always used with SELECT,INSERT,UPDATE,& DELETE to find or filter the results. We can't use this together with GROUP BY.
-- 2. Having : Having clause is always used with GROUP BY clause. seems similar with WHERE, but it's only used together with GROUP BY. 
-- ref : https://www.javatpoint.com/mysql-having
-- ref : https://www.javatpoint.com/mysql-where

-- Write a query in SQL to create Customer and Order table
    CREATE TABLE customers (
        customer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        first_name VARCHAR(100) NOT NULL,
        last_name VARCHAR(100) NOT NULL,
        birthday DATE NOT NULL,
        tel VARCHAR(100),
        address TEXT NOT NULL,
        city VARCHAR(100) NOT NULL,
        points INT NOT NULL
    );

    CREATE TABLE orders (
        order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        customer_id	INT NOT NULL,
        order_date DATE NOT NULL,
        status BOOLEAN,
        comments VARCHAR(255),customers
        CONSTRAINT FK_customerOrder 
            FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    );

--  SQL statement to make a list with customer id, first name, last name, birthday, order id and order date
    SELECT C.customer_id, C.first_name, C.last_name, C.birthday, O.order_id, O.customer_id
    FROM customers C
    INNER JOIN orders O ON C.customer_id = O.customer_id;
    
--  SQL statement that gives the first name, last name, birthday and address of the customers whose order date is after 2018-03-01 (year/month/day) and who commented on.
    SELECT C.customer_id, C.first_name, C.last_name, C.birthday, O.order_id, O.customer_id
    FROM customers C
    INNER JOIN orders O ON C.customer_id = O.customer_id
    WHERE O.order_date > '2018-03-01' AND O.comments IS NOT NULL;
    
-- Define ACID Properties in a Relational Databases
-- ACID Properties is used in order to maintain consistency in a database, before and after transactions, ACID includes 4 properties, there are : Atomicity, Consistency, Isolation, Durability.
-- ref : https://www.geeksforgeeks.org/acid-properties-in-dbms/

-- What are NoSQL Database ?
-- Nosql is the opposite of sql database. NoSQL databases are purpose-built for specific data models and have flexible schemas for building modern applications.
-- ref : https://www.mongodb.com/nosql-explained#:~:text=MongoDB%20is%20consistently%20ranked%20as,item%20contains%20keys%20and%20values.

-- SQL & NO SQL Differences
-- SQL databases are relational, NoSQL are non-relational. SQL databases use structured query language and have a predefined schema. NoSQL databases have dynamic schemas for unstructured data.
-- ref : https://www.geeksforgeeks.org/difference-between-sql-and-nosql/

-- What kind of NoSQL database MongoDB is? 
-- MongoDB is one of Many No SQL Databases. and it is a document based database. We store the data using document model with BSON format. And we use javascript to query the datas.
-- ref: https://www.guru99.com/what-is-mongodb.html