Please enable JavaScript.

Coggle requires JavaScript to display documents.

CHAPTER 4: SQL, Dannellia, Dannellia, Dannellia, Dannellia, Mauldorna,…

- - - - DELETE PRIMARY KEY:
        ALTER TABLE
        DEPARTMENT
        DROP PRIMARY KEY ;
      - CREATE PRIMARY KEY
        USING ALTER TABLE:
        ALTER TABLE
        DEPARTMENT
        ADD PRIMARY KEY (dept_id);
        ALTER TABLE CUSTOMERS
        ADD PRIMARY KEY(ID,NAME);
        
        Haziq
      - Dannellia
    - - Ensures that all values in a column satisfies certain conditions
    - - Provides a default value to a column when the INSERT INTO statement does not provide a specific value
    - - Ensures that all values in a column are different
  - - - SELECT address FROM employee;
      - SELECT DISTINCT address FROM employee;
      - Haziq
        
        Try in SQL WORKBENCH, database sakila:
        SELECT RATING FROM FILM;
        SELECT DISTINCT RATING FROM FILM;
  - - - SELECT column_name(s) From table_name ORDER BY column_name;
      - Default order is ascending
    - - Example: to display the company names in alphabetical order
        SELECT Company, Order Number
        FROM Orders
        ORDER by Company;
  - - - Renaming a Table
        
        Basic Syntax:
        ALTER TABLE table_name RENAME TO new_table_name;
        Example
        ALTER TABLE employee2 RENAME TO staff;
        Rename employee2 table to staff
  - - - Haziq
        
        SELECT ID, NAME, AMOUNT, DATE
        FROM CUSTOMERS
        LEFT JOIN ORDERS
        ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
      - Haziq
        
        INTERSECT
        SELECT ID, NAME, AMOUNT, DATE,
        FROM CUSTOMERS RIGHT JOIN ORDERS
        ON CUSTOMERS.ID = ORDERS.CUSTOMERS_ID;
- - - - Length bytes
        
        String length + 1 bytes
        
        String length + 1 bytes
        
        String length + 2 bytes
        
        String length + 3 bytes
        String length + 4 bytes
        
        1 bytes
        
        2 bytes
        
        3 bytes
        
        4 bytes
        
        8 bytes
        
        Length +1 bytes or
        Length + 2 bytes
        
        3 bytes
        
        3 bytes
        
        1 or 2 bytes
    - - (C) can be from 0 to 255
        
        (VC) can be from 0 to 255
        
        (TT) maximum length of 255 characters
        
        (T) maximum length of 65,535 characters
        
        (MT) maximum length of 16,777,215 characters
        
        (LT) maximum length of 4,294,967,295 characters
        
        (TY) range is from -128 to 127. Unsigned range is from 0 to 255.
        
        (SL) A small integer. Signed range is from - 32768 to 32767. Unsigned range is from 0 to 65535
        
        (MM) Signed range is from -8388608 to 8388607. Unsigned range is from 0 to 16777215.
        
        Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned
        
        Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned
        
        A small number with a floating decimal point.
        
        A large number with a floating decimal point.
        
        A DOUBLE stored as a string, allowing for a fixed decimal point.
        
        Short for enumeration, that is, each column can have one of several possible values.
    - - CHAR [length]
        
        VARCHAR [length]
        
        TINYTEXT
        
        TEXT
        
        MEDIUMTEXT
        
        LONGTEXT
        
        TINYINT [length]
        
        SMALLINT [length]
        
        MEDIUMINT [length]
        
        INT[Length]
        
        BIGINT[Length]
        
        FLOAT
        
        DOUBLE[Length, Decimals]
        
        DECIMAL[Length, Decimals]
        
        DATE
        
        DATETIME
        
        TIMESTAMP
        
        TIME
  - - - DELETE FROM table_name
    - - Result :
  - - - The pattern is a sequence of characters to search for in the column or expression.
        
        It can include the following valid wildcard characters:
        
        The percent wildcard (%): any string of zero or more characters.
        
        The underscore (_) wildcard: any single character.
        
        Example
        
        SELECT title
        FROM film
        WHERE tile like ‘J%’;
- - - - SQL is an ANSI and ISO standard computer language for creating and manipulating databases.
      - SQL allows the user to create, update, delete, and retrieve data from a database.
      - SQL is very simple and easy to learn.
      - Non-procedural language.
      - Consists of standard English words
  - - - CREATE TABLE department
        (
        dept_id int(3) PRIMARY KEY,
        dept_name varchar(20) NOT NULL
        );
        
        ALTER TABLE department MODIFY dept_name varchar(20) NOT NULL;
    - - ALTER TABLE department
        MODIFY dept_name varchar(20) NULL;
  - - - ALTER TABLE table_name DROP COLUMN column_name;
        
        For example:
        
        ALTER TABLE staff DROP COLUMN city;
        
        This will drop the column called city from the table called staff.
  - - - SELECT column_name(s)
      - FROM table_name
      - WHERE condition(s);
- - - - Example: USE mydatabase;
- - - - DUAL is a public table that you can use to view results from functions and calculations.
- - - - Before:
      - After:
- - - - use: create and modify the structure
      - example: CREATE,ALTER,USE,DROP
    - - use: Retrieve,store,modify,delete,insert and update data
      - example: UPDATE,INSERT...INTO,DELETE,SELECT
    - - use: create roles,permissions and referential integrity. used to control access to database
      - example:GRANT,REVOKE
    - - use: manage transaction in database
      - manage the changes made by DML
      - allow statement to be grouped together
- - - - ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL CHECK (AGE>=18);
        
        ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint CHECK (AGE>=18);
        
        Support naming the constraint in multiple columns
        
        DELETE CHECK CONSTRAINT : ALTER TABLE CUSTOMERS ADD CONSTRAINT myCheckConstraint Check (age>=18)
        
        does not work with MySQL as MySQL does not support check constraint
- - - - Rename column
        
        To rename a column, the ALTER TABLE syntax is:
        ALTER TABLE table_name
        CHANGE old_name new_name data_type;
        
        Exp:
        ALTER TABLE staff
        CHANGE email email_address varchar(50);
        
        COMPARISON & LOGICAL OPERATOR
        
        AGGREGATE FUNCTIONS
        
        COUNT EXAMPLE:
        SELECT COUNT( film_id )
        FROM film
        WHERE special_features LIKE
        ‘%Deleted Scenes%';
- - - - SELECT title, rental_rate
        FROM film
        WHERE title LIKE ‘R%’
        AND rental_rate = 2.99;
      - SELECT title, rental_rate
        FROM film
        WHERE title LIKE ‘R%’
        OR rental_rate = 2.99;