arizaljava.blogspot.my

Information about normalization: https://www.studytonight.com/dbms/first-normal-form.php

Resource: Database Systems Design, Implemetation and Management(Coronel, Morris)

MySQL Subquery Summary : in this tutorial, we will show you how to use the  MySQL subquery  to write complex queries and explain the correlated subquery concept. A MySQL subquery is a query nested within another query such as  SELECT ,  INSERT ,  UPDATE   or  DELETE . In addition, a MySQL subquery can be nested inside another subquery. A MySQL subquery is called an inner query while the query that contains the subquery is called an outer query. A subquery can be used anywhere that expression is used and must be closed in parentheses. The following query returns employees who work in the offices located in the USA. 1 2 3 4 5 6 7 8 9 10 11 SELECT      lastName, firstName FROM      employees WHERE      officeCode IN ( SELECT              officeCode          FROM              offices          WHERE              country = 'USA' ); Resource:  http://www.mysqltutorial.org/mysql-subquery/

Grouping The SQL  GROUP BY  clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause. Syntax The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used. SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2 Example: SQL > SELECT NAME , SUM ( SALARY ) FROM CUSTOMERS GROUP BY NAME ; Resource: https://www.tutorialspoint.com/sql/sql-group-by.htm

Aggregate functions in DBMS take multiple rows from the table and return a value according to the query. All the aggregate functions are used in Select statement. Syntax:  SELECT < FUNCTION NAME > (< PARAMETER >) FROM < TABLE NAME > AVG Function This function returns the average value of the numeric column that is supplied as a parameter. Example: Write a query to select average salary from employee table. Resource:  https://www.tutorialspoint.com/Aggregate-Functions-in-DBMS

link ke system

\ Reference about join table Reference inner join Reference left join Reference right join Reference full join Reference self join

CREATE USER 'jeffrey' @ 'localhost' IDENTIFIED BY ' password ' ; GRANT SELECT ON mydbschema . * TO 'someuser' @ 'localhost' ;

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW Syntax CREATE   VIEW  view_name  AS SELECT  column1, column2, ... FROM  table_name WHERE  condition; SQL CREATE VIEW Examples The following SQL creates a view that shows all customers from Brazil: Example CREATE   VIEW  [Brazil  Customers]  AS SELECT   CustomerName, ContactName FROM  Customers WHERE   Country =  "Brazil" ;

SQL CREATE INDEX Statement The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries CREATE INDEX Syntax Creates an index on a table. Duplicate values are allowed: CREATE   INDEX   index_name ON   table_name  ( column1 ,  column2 , ...); Example: CREATE   INDEX  idx_lastname ON  Persons (LastName); Example: CREATE   INDEX  idx_pname ON  Persons (LastName, FirstName); Source:  https://www.w3schools.com/sql/sql_create_index.asp

CREATE   TABLE   new_table_name   AS      SELECT   column1, column2,...      FROM   existing_table_name      WHERE  ....; Example: CREATE   TABLE  TestTable  AS SELECT  customername, contactname FROM   customers ; Example:  CREATE   TABLE  TestTable  AS SELECT  customername, contactname FROM   customers  WHERE customername ='Siti' ; Source:  https://www.w3schools.com/sql/sql_create_table.asp