CREATE TABLE person(
  id_person SMALLINT primary key
  ...

  CREATE TABLE person(
  id_person SMALLINT,
  ...
  PRIMARY KEY(id_person)
  ...

  PRIMARY KEY (id_project, id_person)

Index in sql is created to tables to retrieve the rows quickly. When there are thousands of records in a table, retrieving information will take a long time. Therefore indexes are created on columns which are accessed frequently, so that the information can be retrieved quickly. Indexes can be created on a single column or a group of columns. When a index is created, it first sorts the data and then it assigns a ROWID for each row.

When you create an index, you can define whether duplicate values are allowed or not. If duplicate values are allowed, the index is created with keyword INDEX.

  CREATE TABLE person(
  id_person SMALLINT PRIMARY KEY,
  firstname CHAR(15) NOT NULL,
  lastname CHAR(15) NOT NULL,
  city CHAR(10),
  birthyear SMALLINT,
  salary DOUBLE,
  INDEX lastnameIndex (lastname));

  CREATE TABLE person(
  id_person SMALLINT PRIMARY KEY,
  firstname CHAR(15) NOT NULL,
  lastname CHAR(15) NOT NULL,
  city CHAR(10),
  birthyear SMALLINT,
  salary DOUBLE,
  UNIQUE lastnameIndex (lastname) );

  CREATE TABLE person(
  id_person SMALLINT PRIMARY KEY,
  firstname CHAR(15) NOT NULL,
  lastname CHAR(15) NOT NULL,
  city CHAR(10),
  birthyear SMALLINT,
  salary DOUBLE,
  INDEX nameIndex (firstname,lastname) );

If you don't give any name to the index, MySQL names the index according to the first index field.
Example INDEX (fi,si) -> index will be named fi.

You can also add indexes later, when the table already exists. Then you can use below code:

     CREATE INDEX lastnameIndex ON person(lastname);
  

You can check what indexes the table has with below command:

    show indexes from person\G
  

You can delete indexes with below command:

    DROP INDEX lastnameIndex ON person;
  

Data types vary slightly in different systems, so you should check the product manual. Most products has below types:

• CHAR(max)
• VARCHAR(max)
• SMALLINT
• INTEGER
• FLOAT
• REAL
• DOUBLE
• DATE
• TIME

The purpose of referential integrity is to take care that all rows in child-table have a parent row in parent table.

In order to create table named hour to MySQL, you can use below code

  CREATE TABLE hour (
  id_project CHAR(5) NOT NULL,
  id_person SMALLINT NOT NULL,
  workhour INT,
  PRIMARY KEY (id_project,id_person),
  CONSTRAINT project_hour
  FOREIGN KEY (id_project) REFERENCES project (id_project)
    ON DELETE RESTRICT
    ON UPDATE CASCADE) Engine=InnoDB;

You can use DISTINCT statement in the SELECT query to return only those lines as a result which have differences.

SELECT DISTINCT city
FROM person;

You can use Limit statement in the SELECT query to choose which lines to return.

SELECT * 
FROM person LIMIT 2;

SELECT * 
FROM person LIMIT 3,2;

There are several functions which you can use in the SELECT. Here are some examples, which works in MySQL.

SELECT DATE();

SELECT VERSION();

SELECT USER();

If you want to change the heading of the result set, you can use the AS keyword

SELECT lastname AS Name
FROM person;

SELECT name, 'Some text'
FROM person;

You can concatenate several fields and literals together

SELECT CONCAT(firstname," ", lastname) AS Name
FROM person;

SELECT SUBSTRING(lastname,2,4) FROM person;

You can use Where statement to choose which rows will be included to the view.

SELECT *
FROM person
WHERE city ='TURKU';

SELECT *
FROM person
WHERE NOT city='TURKU';

SELECT *
FROM person
WHERE city <>'TURKU';

SELECT *
FROM person
WHERE salary=5000;

WHERE city='Turku' OR city='Tampere' OR city='Oulu'

WHERE city in ('Turku', 'Tampere', 'Oulu')

WHERE salary >= 4000 AND salary <= 5000

WHERE salary BETWEEN 4000 AND 5000

SELECT *
FROM project
WHERE place IS NULL;

WHERE city LIKE 'T%'

WHERE city LIKE '_i%'

WHERE city LIKE '%i%'

The Aggregate Functions are:

• AVG
• MIN
• MAX
• COUNT
• SUM
• GROUP_CONCAT

SELECT AVG(salary)
FROM person;

SELECT city, count(id_person)
FROM person
GROUP BY city;

SELECT city, GROUP_CONCAT(lastname) AS peoples
FROM person
GROUP BY city;

+----------+----------------------+
| city     | peoples              |
+----------+----------------------+
| HELSINKI | River,Simpson        |
| TAMPERE  | Daniels              |
| TURKU    | Morrison,Smith,Jones |
+----------+----------------------+

SELECT place, count(*)
FROM project
GROUP BY place
HAVING COUNT (*) >= 2;

SQL join clause combines records from two or more tables in a database. You can combine the parent and child table with JOIN and then return fields from both tables. MySQL has three kind of JOINS:

• INNER JOIN
• LEFT OUTER JOIN (or LEFT JOIN)
• RIGHT OUTER JOIN (or RIGHT JOIN)

Inner Join

Inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row
The join is made in FROM clause. For example Table1 and Table2 both have a field called id (in most cases id is the primary key in one table and foreign key in the other table). The inner joint is created between them as follows:

FROM Table1 INNER JOIN Table2 ON Table1.id=Table2.id

Example Creating an inner join between tables hour and project.

select project.id_project,pname,work_hour
FROM project inner JOIN hour ON project.id_project = hour.id_project;

And the result is this

+------------+-------------+-----------+
| id_project | pname       | work_hour |
+------------+-------------+-----------+
|        101 | Bookkeeping |       300 |
|        101 | Bookkeeping |       200 |
|        101 | Bookkeeping |       200 |
|        101 | Bookkeeping |       100 |
|        101 | Bookkeeping |       100 |
|        101 | Bookkeeping |       400 |
|        102 | Billing     |       300 |
|        102 | Billing     |       400 |
|        103 | Store       |       200 |
|        104 | Selling     |       300 |
|        104 | Selling     |       200 |
|        104 | Selling     |       400 |
+------------+-------------+-----------+

FROM Table1 LEFT OUTER JOIN Table2 ON Table1.id=Table2.id

FROM Table2 RIGHT OUTER JOIN Table1 ON Table2.id=Table1.id

select project.id_project,pname,work_hour
FROM project left JOIN hour ON project.id_project = hour.id_project;

+------------+-------------+-----------+
| id_project | pname       | work_hour |
+------------+-------------+-----------+
|        101 | Bookkeeping |       300 |
|        101 | Bookkeeping |       200 |
|        101 | Bookkeeping |       200 |
|        101 | Bookkeeping |       100 |
|        101 | Bookkeeping |       100 |
|        101 | Bookkeeping |       400 |
|        102 | Billing     |       300 |
|        102 | Billing     |       400 |
|        103 | Store       |       200 |
|        104 | Selling     |       300 |
|        104 | Selling     |       200 |
|        104 | Selling     |       400 |
|        105 | Customers   |      NULL |
|        106 | Statistics  |      NULL |
+------------+-------------+-----------+

FROM person INNER JOIN hour ON person.id_person=hour.id_person
  INNER JOIN project ON hour.id_project=project.id_project;

You can retrieve records from several tables using Union Query

Example
The persons which salary is over 40 and/or which belong to the project 102

SELECT lastname as Name,"Salary is over 40"
FROM person
WHERE salary>40
UNION
SELECT lastname,"Belongs to 102"
FROM person INNER JOIN hour ON person.id_person = hour.id_person
WHERE id_project='102';

+----------+-------------------+
| Name     | Salary is over 40 |
+----------+-------------------+
| Morrison | Salary is over 40 |
| Jones    | Salary is over 40 |
| Morrison | Belongs to 102    |
| River    | Belongs to 102    |
+----------+-------------------+

SELECT lastname as Name,"Salary is over 40"
FROM person
WHERE salary>40
UNION 
SELECT lastname as Name,"Salary is not over 40"
FROM person
WHERE salary<=40;

+----------+-----------------------+
| Name     | Salary is over 40     |
+----------+-----------------------+
| Morrison | Salary is over 40     |
| Jones    | Salary is over 40     |
| Smith    | Salary is not over 40 |
| River    | Salary is not over 40 |
| Simpson  | Salary is not over 40 |
| Daniels  | Salary is not over 40 |
+----------+-----------------------+

SELECT lastname as Name, 
  IF(salary>40, "Salary is over 40", "Salary is not over 40") as "Explanation"
FROM person;

  SELECT lastname,"Less than 39"
  FROM person
  WHERE salary<39
  UNION
  SELECT lastname,"Over 39"
  FROM person
  WHERE salary>39
  UNION SELECT lastname,"Exactly 39"
  FROM person
  WHERE salary=39
  ORDER BY 2;

+----------+--------------+
| lastname | Less than 39 |
+----------+--------------+
| Smith    | Exactly 39   |
| River    | Exactly 39   |
| Simpson  | Less than 39 |
| Daniels  | Less than 39 |
| Morrison | Over 39      |
| Jones    | Over 39      |
+----------+--------------+

You can use also IF in SELECT statement. The syntax is IF(condition, Option1, Option2). So, if the condition is true, Option1 will be executed otherwise Option2.

Example

SELECT pname, IF(priorit<2, "Very Important", "Normal priorit") as Category
FROM project;

Subquery or Inner query or Nested query is a query inside a query. A subquery is usually added in the WHERE Clause of the sql statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value.

Example All the person are fetched who were born in the same year as Morrison

SELECT lastname
FROM person
WHERE birth_year=
( SELECT birth_year
  FROM person
  WHERE lastname='Morrison');

SELECT lastname, salary
FROM person
WHERE salary >
( SELECT min(salary)*2
  FROM person);

SELECT lastname
FROM person
WHERE id_person IN
( SELECT id_person
  FROM hour
  WHERE work_hour>=400);

WHERE salary > ANY ("SubQuery") 

WHERE salary > ALL ("SubQuery")

+----------+--------+
| city     | salary |
+----------+--------+
| HELSINKI |     39 |
| HELSINKI |     36 |
| TAMPERE  |     34 |
| TURKU    |     44 |
| TURKU    |     39 |
| TURKU    |     44 |
+----------+--------+

select city, salary from person where salary <
	any(select salary from person where city='Turku');

+----------+--------+
| city     | salary |
+----------+--------+
| TURKU    |     39 |
| HELSINKI |     39 |
| HELSINKI |     36 |
| TAMPERE  |     34 |
+----------+--------+

select city, salary from person where salary <
	all(select salary from person where city='Turku');

+----------+--------+
| city     | salary |
+----------+--------+
| HELSINKI |     36 |
| TAMPERE  |     34 |
+----------+--------+

You can also read data from one table and insert that data to a new table which does not already exist. The new table gets the same field definitions as in that table at which the data is picked up.

Example
Below code will read data from project-table and create a new table named helsinki with that data.

  CREATE TABLE helsinki
  SELECT *
  FROM project
  WHERE place='Helsinki';

  INSERT INTO helsinki
  SELECT *
  FROM project
  WHERE place ='Helsinki';

In the parameter list you will create variables (like sname in previous example ), but you can also save the results from SQL to variables. You can create two kind of variables :

• User-defined variables (name starts with symbol @)
• Local variables (using the keyword DECLARE)

Another difference between local variables and user-defined variable is that local variable is reinitialized with NULL value each time whenever stored procedure is called while user-defined variable is not reinitialized with NULL.

  SELECT firstname INTO @chosenFname FROM person WHERE lastname=sname;

  SELECT * FROM person WHERE firstname = @chosenFname;
  

  DECLARE chosenFname VARCHAR(50);
  SELECT firstname INTO chosenFname FROM person WHERE lastname=sname;

  SELECT * FROM person WHERE firstname = chosenFname;
  

Lets assume that you want to check, if some rows exists. So, you can do it like this

DECLARE test INT DEFAULT 0;
SET test = (SELECT COUNT(*) ...);

IF test THEN
    ... 
ELSE 
END IF;

Delimiter is the "string" that tells to mysql-client that you want to execute your script. The default is ;. When you write procedures, you need to add ; inside your procedure code. So, before you start to write the procedure, you should change the delimiter to something else (typically // or $$), so that your client does not execute it immediatelly. And when your procedure is ready, you can change the delimiter back to default.

Create a table with below code

  CREATE TABLE bank_account(
  idaccount INT PRIMARY KEY,
  balance double,
  credit_limit double
  );
  

  CREATE TABLE action (
  idaction int PRIMARY KEY AUTO_INCREMENT,
  idaccount int,
  action varchar(45),
  action_time timestamp,
  amount double
  );
  

  INSERT INTO bank_account VALUES(1,500,1000);
  INSERT INTO bank_account VALUES(2,500,2000);
  

DELIMITER //
CREATE PROCEDURE debit_transfer(IN first_id INT, IN second_id INT, IN amount DOUBLE )
  BEGIN
  DECLARE test1,test2 INT DEFAULT 0;
  START TRANSACTION;
  UPDATE bank_account SET balance=balance-amount WHERE idaccount=first_id AND balance >= amount;
  SET test1=ROW_COUNT();
  UPDATE bank_account SET balance=balance+amount WHERE idaccount=second_id;
  SET test2=ROW_COUNT();
    IF (test1 > 0 AND test2 >0) THEN   
      COMMIT;    
      INSERT INTO action(idaccount,action,amount,action_time) VALUES(first_id,'withdrawal',amount,NOW());
      INSERT INTO action(idaccount,action,amount,action_time) VALUES(second_id,'deposit',amount,NOW());
    ELSE
      ROLLBACK;
  END IF;
  END //
DELIMITER ;

  

• If first account does not exists or there is not enough money, variable test1 = 0
• If second account does not exists, variable test2 = 0
• If both test1 and test2 are bigger than 0, we will execute commit, otherwise we will execute rollback

  CALL debit_transfer(1,2,100);
  SELECT * FROM bank_account;

  and

  CALL debit_transfer(1,3,100);
  SELECT * FROM bank_account;

  and

  CALL debit_transfer(3,2,2000);
  SELECT * FROM bank_account;
  

DELIMITER //
CREATE PROCEDURE credit_transfer(IN first_id INT, IN second_id INT, IN amount DOUBLE )
BEGIN
  DECLARE test1, test2 INT DEFAULT 0;
  START TRANSACTION;
  UPDATE bank_account SET balance=balance-amount WHERE idaccount=first_id AND balance + credit_limit >= amount;
  SET test1=ROW_COUNT();
  UPDATE bank_account SET balance=balance+amount WHERE idaccount=second_id;
  SET test2=ROW_COUNT();
    IF (test1 > 0 AND test2 >0) THEN
      COMMIT;
      INSERT INTO action(idaccount,action,amount,action_time) VALUES(first_id,'withdrawal',amount, NOW());
      INSERT INTO action(idaccount,action,amount,action_time) VALUES(second_id,'deposit',amount, NOW());
    ELSE
      ROLLBACK;
  END IF;
END //
DELIMITER ;