Databases

Relational databases : Querying

• Having modeled your data allows you to store every information properly
• To modify and retrieve data, we need to operate ISUD operations
• ISUD stands for INSERT, SELECT, UPDATE, DELETE
• In the previous lectures, we learnt how to create tables with the CREATE verb, and modify any structural information with ALTER. Now we will act on data

Relational databases : Inserting with "INSERT"

• To insert new data in a table, you have to know its structure
• Hereafter is a sample INSERT statement

  INSERT into facilities(facid, name, membercost, guestcost, initialoutlay, monthlymaintenance)
                 values (9, 'Swimming Pool', 8, 16, 3000,3000)

• Even if the first parenthesis is optional, it is strongly recommended to use it, it gives a clear idea of what are the data inserted

Relational databases : Updating with "UPDATE"

• To update lines of a table, you have to know its structure
• Hereafter is a sample UPDATE statement

  UPDATE facilities SET membercost=10 WHERE name='Swimming Pool';

• notice the SET operator that targets a column, and replace the old value
• some where clause can be used to precise the target
Before using an UPDATE query, always do a select to make like a backup of data before the update

Relational databases : deleting with "DELETE"

• To delete lines of a table, you can use the DELETE, as shown below

  DELETE from facilities WHERE name='Swimming Pool';

• Some where clause can be used to precise the target
Before using a DELETE query, always do a select to make like a backup of data before the update

Relational databases : Querying

• SELECT verb in combination with FROM operator
• The result of this select is a relation containing tuples
• One can select specific columns of an existing table, specified by FROM
• Some filters can be applied thanks to the WHERE clause
• logical & arithmetical operators can be used (=, <,>, <>, AND, OR) to combine several conditions

SQL Queries : practising with MySQL

• Connect to MySQL using your preferred tool (MySQL Workbench, DBeaver, command line)
• Load the data present in the attached clubdata (credits : https://pgexercises.com/)
• Run the query:

  SELECT * FROM bookings;

  Tip for MySQL : Make sure you're using the correct database

  SHOW DATABASES;
  USE your_database_name;
  SHOW TABLES;

Select with WHERE clauses : practising

• You can refine the previous query by adding a where clause

  SELECT * FROM facilities
  WHERE monthlymaintenance < 1000;

• will produce
  "facid","name","membercost","guestcost","initialoutlay","monthlymaintenance" 0,"Tennis Court 1","5","25","10000","200" 1,"Tennis Court 2","5","25","8000","200" 2,"Badminton Court","0","15.5","4000","50" 3,"Table Tennis","0","5","320","10" 6,"Squash Court","3.5","17.5","5000","80" 7,"Snooker Table","0","5","450","15" 8,"Pool Table","0","5","400","15"

JOIN operators

• You can combine data coming from different tables by usign the JOIN operator, for example

  SELECT * FROM facilities
  JOIN bookings on bookings.facid = facilities.facid
  WHERE monthlymaintenance < 15;

• will produce
  "facid","name","membercost","guestcost","initialoutlay","monthlymaintenance","bookid","facid-2","memid","starttime","slots" 3,"Table Tennis","0","5","320","10",0,3,1,"2012-07-03 11:00:00",2 6,"Squash Court","3.5","17.5","5000","80",2,6,0,"2012-07-03 18:00:00",2 7,"Snooker Table","0","5","450","15",3,7,1,"2012-07-03 19:00:00",2 8,"Pool Table","0","5","400","15",4,8,1,"2012-07-03 10:00:00",1 8,"Pool Table","0","5","400","15",5,8,1,"2012-07-03 15:00:00",1

Different types of JOIN

credits visual representation of SQL-Joins

Exercise : practising Select with joins

• Download and analyse this sql dump : tables
• Convert this SQL Script into a PostgreSQL script, and import it in a new database
• Try all the sorts of SQL Joins, as shown in the previous schema

Exercise : Going further with SQL Queries

• To practise better the PostgreSQL environment, achieve the following exercises
• pgexercises

Integrating with Programming languages

• With Python (using MySQL connector)

  import mysql.connector
  
  connection = mysql.connector.connect(
      host="localhost",
      user="root",
      password="your_password",
      database="your_database"
  )
  cursor = connection.cursor()
  cursor.execute("SELECT * FROM customers")
  results = cursor.fetchall()
          

  Install with: pip install mysql-connector-python

Integrating with Programming languages (2)

• with Nodejs (using mysql2)

  const mysql = require('mysql2/promise');
  
  const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: 'your_database'
  });
  
  const [rows] = await connection.execute('SELECT * FROM customers');
  console.log(rows);
  

  Install with: npm install mysql2

Integrating with Programming languages(3)

• with Java (using MySQL Connector/J)

  import java.sql.*;
  
  public class MySQLExample {
      public static void main(String[] args) {
          String url = "jdbc:mysql://localhost:3306/your_database";
          String user = "root";
          String password = "your_password";
          
          try (Connection conn = DriverManager.getConnection(url, user, password);
               Statement stmt = conn.createStatement();
               ResultSet rs = stmt.executeQuery("SELECT * FROM customers")) {
              
              while (rs.next()) {
                  System.out.println(rs.getString("name"));
              }
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  }
  

  Add MySQL Connector/J dependency to your project

Exercise: Practice Basic Queries

Using the facilities/bookings database, write queries for:

1. List all facilities with monthly maintenance cost greater than $100
2. List all members who joined after January 1st, 2012
3. Count how many bookings were made by member ID 1
4. Find the total number of slots booked per facility
5. List facilities that have never been booked

-- Example solution for #1:
SELECT * FROM facilities 
WHERE monthlymaintenance > 100;
    

Exercise: Practice JOINs

Write queries that combine multiple tables:

1. List all bookings with member names (not IDs)
2. Show facility names with their total booking revenue
3. Find members who have never made a booking
4. List the top 5 most popular facilities by number of bookings

-- Example solution for #1:
SELECT b.*, m.firstname, m.surname 
FROM bookings b
INNER JOIN members m ON b.memid = m.memid;
    

MySQL String Functions

MySQL Date Functions