arizaljava.blogspot.my

Click this link to view the lists of mysql function https://www.w3schools.com/sql/sql_ref_mysql.asp

Resource:  https://vegibit.com/working-with-common-mysql-string-functions/

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

MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator. Example If you want to create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are 'USA','UK' and 'India', the following statement can be used. CREATE TABLE IF NOT EXISTS newauthor ( aut_id varchar ( 8 ) NOT NULL , aut_name varchar ( 50 ) NOT NULL , country varchar ( 25 ) NOT NULL CHECK ( country IN ( 'USA' , 'UK' , 'India' ) ) , home_city varchar ( 25 ) NOT NULL , PRIMARY KEY ( aut_id , home_city ) ) ; Resource:https://www.w3resource.com/mysql/creating-table-advance/constraint.php

Add primary key constraints ALTER   TABLE  Persons ADD   PRIMARY   KEY  (ID); Add not null constraints ALTER TABLE table_name MODIFY column_name datatype NOT NULL; Resource: https://www.tutorialspoint.com/sql/sql-alter-command.htm

Key: (Part of) Primary Key Filled Diamond: NOT NULL Not filled Diamond: NULL Red colored: (Part of) Foreign key Blue lined Diamond: Simple attribute (no key) Can be combined for example:  is a Red colored Key so it's a Primary Key which is also a Foreign Key  is a Yellow (non Red) Key so it's only a Primary Key  is a blue lined filled diamond so it's a NOT NULL simple attribute  is a red colored filled diamond so it's a NOT NULL Foreign Key  is a blue lined not filled diamond so it's a simple attribute which can be NULL  is a red colored not filled diamond so it's a Foreign Key which can be NULL Resource: https://stackoverflow.com/questions/10778561/what-do-the-mysql-workbench-column-icons-mean/28859886#28859886

CREATE   TABLE   table_name  (     column1 datatype   constraint ,     column2 datatype   constraint ,     column3 datatype   constraint ,     .... ); Resource:  https://www.w3schools.com/sql/sql_constraints.asp

Create Table syntax CREATE   TABLE   table_name  (     column1 datatype ,     column2 datatype ,     column3 datatype ,    .... ); Example CREATE   TABLE  Persons (     PersonID int,     LastName varchar( 255 ),     FirstName varchar( 255 ),     Address varchar( 255 ),     City varchar( 255 )  ); Resource:  https://www.w3schools.com/sql/sql_create_table.asp

This is a really helpfull article about exception handling in java https://www.tutorialspoint.com/java/java_exceptions.htm

Final Classes and Methods  Inheritance is surely one of the highly useful features in Java. But at times, it may be desired that a class should not be extendable by other classes to prevent exploitation. For such purpose, we have the final keyword. We have already seen even what final variables are. Final classes and methods are also similar. A class declared as final cannot be extended while a method declared as final cannot be overridden in its subclasses. A method or a class is declared to be final using the final keyword. Though a final class cannot be extended, it can extend other classes. In simpler words, a final class can be a sub class but not a super class. final public class A {     //code } Resource:  http://www.javawithus.com/tutorial/final-classes-and-methods

A class that is declared with abstract keyword, is known as abstract class in java. It can have abstract and non-abstract methods (method with body). Before learning java abstract class, let's understand the abstraction in java first. Abstraction in Java Abstraction  is a process of hiding the implementation details and showing only functionality to the user. Another way, it shows only important things to the user and hides the internal details for example sending sms, you just type the text and send the message. You don't know the internal processing about the message delivery. Abstraction lets you focus on what the object does instead of how it does it. example: In this example, Bike the abstract class that contains only one abstract method run. It implementation is provided by the Honda class. abstract   class  Bike{      abstract   void  run();   }   class  Honda4  extends  Bike{   void  run(){System.out.println( "running safely.." );}