Please enable JavaScript.

Coggle requires JavaScript to display documents.

CHAPTER 4: SQL, AGGREGATE FUNCTIONS SQL aggregate functions: image, AFIQ…

- - - - CREATE TABLE employee2
        AS
        SELECT * FROM employee;
        OR
        CREATE TABLE employee2
        AS
        SELECT emp_id, emp_name
        FROM employee;
        -SARRAH-
        
        DISPLAYING TABLE STRUCTURE
        Use DESCRIBE or DESC command to display the structure of a table.
        Example: DESCRIBE employee;
        DESC employee;
        -SARRAH-
        
        DELETE TABLE
        DROP TABLE
        Syntax: DROP TABLE table_name;
        Example: DROP TABLE employee2;
        Will delete the entire table.
        -SARRAH-
        
        MODIFYING TABLES
        The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.
        Syntax:
        ALTER TABLE table_name (action field_definition, action field_definition,..);
        -SARRAH-
        
        RENAMING A TABLE
        
        The basic syntax for renaming a table is:
        ALTER TABLE table_name;
        RENAME TO new_table_name
        
        For example:
        ALTER TABLE employee2
        RENAME TO staff;
        
        This will rename the employee2 table to staff.
        
        ERITNEY MAJA
        
        ADD COLUMN
        
        To add column to an existing table, the
        ALTER TABLE syntax is:
        ALTER TABLE table_name ADD COLUMN name_column-definition;
        
        For example:
        ALTER TABLE staff ADD email varchar(50);
        
        This will add a column called email to the staff table.
        
        ERITNEY MAJA
      - Add multiple columns -
        -To add multiple columns to an existing table, the ALTER TABLE syntax is:
        ALTER TABLE table_name
        ADD ( column_1 column-definition,
        column_2 column-definition,
        column_n column_definition );
        -For example:
        ALTER TABLE staff
        ADD ( email varchar(50),
        city varchar(45) );
        -This will add two columns (email and city) to the staff table.
        -nursyuhadda ain-
      - Modify a column
        -To modify a column in an existing table, the
        ALTER TABLE syntax is:
        ALTER TABLE table_name
        MODIFY column_name column_type;
        -For example:
        ALTER TABLE staff
        MODIFY email varchar(100) not null;
        -This will modify the column called email to be a data type of varchar(100) and force the column to not allow null values.
        -nursyuadda ain-
      - Delete column
        • To drop a column in an existing table, the ALTER TABLE syntax is:
        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.
        ⮞ MARCELLA
      - Rename column
        • To rename a column, the ALTER TABLE syntax is:
        ALTER TABLE table_name
        CHANGE old_name new_name data_type;
        • Example:
        ALTER TABLE staff
        CHANGE email email_address varchar(50);
        ⮞ MARCELLA
      - Update one Column in a Row
        Add a first name to the person with a last name of "Rahmat":
        Update one Column in a Row
        Add a first name to the person with a last name of "Rahmat":
        Najmil
      - Update several columns in a row:
        If we want to change the address and name of the city:
        UPDATE Person
        SET Address = ‘Gita 12', City =‘Kuching’
        WHERE LastName = 'Rahmat'
        Najmil
      - REMOVE RECORDS - DELETE
        =DELETE - to delete records in a table.
        =Syntax:
        DELETE FROM table name
        WHERE column name=some_value;
        =Consider this table:Person
        
        OLIVIA
        
        Example
        =To delete a row - deleting Nina Rahmat record.
        DELETE FROM Person
        WHERE LastName='Rahmat';
        =Result:
        
        = To delete all rows:
        -It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:
        DELETE FROM table_name
        Example:
        DELETE FROM Person;
        OLIVIA
      - REMOVE RECORDS - TRUNCATE
        =TRUNCATE command will removes all rows from a table, leaving the table empty and the table structure intact.
        =DDL statement rather than DML (which is DELETE).
        =Syntax: TRUNCATE TABLE table_name;
        =Example: TRUNCATE TABLE Person;
        =Difference with DELETE command:
        -With DELETE, you can use WHERE (specify conditions)
        you can't use WHERE with TRUNCATE
        
        VICTOR
- - - - EXAMPLE(using MySQL Workbench, database sakila)
        
        -Sabrynna
      - USING COLUMN ALIAS(using MySQL Workbench, database sakila)
        Sabrynna
  - - - Result -Rose -
    - - GROUP BY
        You can divide rows in a table into smaller groups by using the GROUP BY clause.
        Syntax:
        SELECT column, aggregate_function(column)
        FROM table
        GROUP BY column ;
        andrew kulleh
        
        EXAMPLE:
        SELECT RATING, COUNT(FILM_ID)
        FROM FILM
        GROUP BY RATING;
        -Anastasia Jyntang-
        
        Having...
        HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.
        Syntax:
        SELECT column ,SUM(column)
        FROM table
        GROUP BY column
        HAVING SUM (column) condition value;
        -Anastasia Jyntang-
        
        INTERSECT
        -syntax:
        SELECT column1 (, column2)
        FROM table1, table2
        (WHERE condition)
        INTERSECT
        SELECT column1 (, column2)
        FROM table1, table2
        (WHERE condition)
        -florence preeti-
        
        EXAMPLE
        
        SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
        INTERSECT
        SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMERS_ID;
        -florence preeti-
        
        1 more item...
- - - - syafiq azwan
        
        EXAMPLE(using MySQL Workbench, database sakila)
        -SELECT first_name,last_name
        FROM customer
        WHERE customer_id = 1;
        -SELECT first_name,last_name
        FROM customer
        WHERE customer_id <= 10;
        syafiq azwan
        
        BETWEEN…AND… operator
        To display rows based on a range values:
        -SELECT film_id, title, replacement_cost
        FROM film
        -WHERE replacement_cost BETWEEN 9.00 AND 12.00;
        syafiq azwan
        
        Using the IN operator
        -Use the IN operator to test for values in a list.
        -Can reduce the need to use multiple OR conditions.
        -IN function can be used with string and numeric values.
        -Example:
        SELECT address_id
        FROM address
        WHERE district IN ('California', 'Nagasaki');
        -It is equivalent to the following statement:
        SELECT address_id
        FROM address
        WHERE district = ‘California’
        OR district = ‘Nagasaki’;
        
        syafiq azwan
        
        NOT IN operator
        IN function can be combined with NOT operator
        SELECT titleFROM filmWHERE rating NOT IN (‘PG');
        nur syahema naziera
        
        Pattern matching using the LIKE operator
        Use the LIKE operator to perform wildcard searches of valid search string values
        Search conditions can contain either literal characters or numbers:
        % denotes zero or many character
        _ denotes one character
        nur syahema naziera
- - - - ORDERS table:
        
        Example: to display the company names in alphabetical order:
        SELECT Company, OrderNumber
        FROM Orders
        ORDER BY Company;
        -PuteriNadiatul-