arizaljava.blogspot.my

 Link to Module Database Lab 4 https://myunitechsolution.com/nextcloud/index.php/s/pkSN57e6ZQWpaYL Lab 5 https://myunitechsolution.com/nextcloud/index.php/s/GxtSSkZB8DM5RTb lab6 https://docs.google.com/document/d/1WIM_8JLVDxGNK1mTl2nQ_3RvdsdVpaaA/edit?usp=sharing&ouid=114277968274457984202&rtpof=true&sd=true

  In MySQL, the ON  UPDATE CASCADE  option in foreign key constraints indicates that anytime an update is made to a row in the parent table, matching rows in child tables are correspondingly updated. This  feature provides   assurance  for referential integrity and consistency of related data in a  related set of tables  without having to manage it manually. Knowing when and how to use ON  UPDATE CASCADE  is important in managing table relationships effectively and facilitating data maintenance. What is ON UPDATE CASCADE? The  ON UPDATE CASCADE  is an option that makes up a definition for a  foreign key  constraint in  MySQL . If a value in the parent table column of the  primary key   is updated, it ensures that the matching value is changed in the  foreign key  column in the  child  table. This may be done through  cascading updates , to keep relationships between related tabl...

 

https://www.studentstutorial.com/java-project/jsp-delete-data.php

To connect Java application with the MySQL database, we need to follow 5 following steps. In this example we are using MySql as the database. So we need to know following informations for the mysql database: Driver class: The driver class for the mysql database is com.mysql.jdbc.Driver . Connection URL: The connection URL for the mysql database is jdbc:mysql://localhost:3306/sonoo where jdbc is the API, mysql is the database, localhost is the server name on which mysql is running, we may also use IP address, 3306 is the port number and sonoo is the database name. We may use any database, in such case, we need to replace the sonoo with our database name. Username: The default username for the mysql database is root . Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use root as the password. Resource:  https://www.javatpoint.com/example-to-connect-to-the-mysql-database

MySQL  DATE_FORMAT()  Function ❮ MySQL Functions Example Format a date: SELECT  DATE_FORMAT( "2017-06-15" ,  "%Y" ); Try it Yourself » Definition and Usage The DATE_FORMAT() function formats a date as specified. Syntax DATE_FORMAT( date ,  format ) Resource:  https://www.w3schools.com/sql/func_mysql_date_format.asp

MySQL  ROUND()  Function ❮ MySQL Functions Example Round the number to 2 decimal places: SELECT  ROUND( 135.375 ,  2 ); Try it Yourself » Definition and Usage The ROUND() function rounds a number to a specified number of decimal places. Note:  See also the  FLOOR() ,  CEIL() ,  CEILING() , and  TRUNCATE()  functions. Syntax ROUND( number ,  decimals ) Resource: https://www.w3schools.com/sql/func_mysql_round.asp

MySQL  LOCATE()  Function ❮ MySQL Functions Example Search for "3" in string "W3Schools.com", and return position: SELECT  LOCATE( "3" ,  "W3Schools.com" )  AS  MatchPosition; Try it Yourself » Definition and Usage The LOCATE() function returns the position of the first occurrence of a substring in a string. If the substring is not found within the original string, this function returns 0. This function performs a case-insensitive search. Note:  This function is equal to the  POSITION()  function. Syntax LOCATE( substring ,  string ,  start ) Resource: https://www.w3schools.com/sql/func_mysql_locate.asp

MySQL  SUBSTRING()  Function ❮ MySQL Functions Example Extract a substring from a string (start at position 5, extract 3 characters): SELECT  SUBSTRING( "SQL Tutorial" ,  5 ,  3 )  AS  ExtractString; Try it Yourself » Definition and Usage The SUBSTRING() function extracts a substring from a string (starting at any position). Note:  The  SUBSTR()  and  MID()  functions equals to the SUBSTRING() function. Syntax SUBSTRING( string ,  start ,  length ) OR: SUBSTRING( string  FROM  start  FOR  length ) Resource:  https://www.w3schools.com/sql/func_mysql_substring.asp

MySQL  LEFT()  Function ❮ MySQL Functions Example Extract 3 characters from a string (starting from left): SELECT   LEFT ( "SQL Tutorial" ,  3 )  AS  ExtractString; Try it Yourself » Definition and Usage The LEFT() function extracts a number of characters from a string (starting from left). Tip:  Also look at the  RIGHT()  function. Syntax LEFT( string ,  number_of_chars ) Resource: https://www.w3schools.com/sql/func_mysql_left.asp

MySQL  CONCAT_WS()  Function ❮ MySQL Functions Example Add several expressions together, and add a "-" separator between them: SELECT  CONCAT_WS( "-" ,  "SQL" ,  "Tutorial" ,  "is" ,  "fun!" )  AS  ConcatenatedString; Try it Yourself » Definition and Usage The CONCAT_WS() function adds two or more expressions together with a separator. Note:  Also look at the  CONCAT()  function. Syntax CONCAT_WS( separator ,  expression1 ,  expression2 ,  expression3 ,...) Resource: https://www.w3schools.com/sql/func_mysql_concat_ws.asp

example SELECT name , price , photo FROM drinks , drinks_photos WHERE drinks . id = drink_photos.drinks_id https://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql

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    ...

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' ;