Skip to main content

MySQL For Beginners

MySQL

Introduction​

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL) to manage and organize data. It is one of the most widely used database management systems in the world, powering millions of websites and applications. If you’re new to MySQL, this beginner’s guide will provide you with a comprehensive introduction to the basics of MySQL, including installation, database creation, and basic SQL commands.

Installation​

The first step to getting started with MySQL is to download and install the software. MySQL is available for Windows, Mac, and Linux, and you can download it from the official MySQL website.

Once you’ve downloaded the software, follow the installation instructions provided. During the installation process, you’ll be asked to choose a username and password for your MySQL server. This username and password will be used to log in to your MySQL server, so make sure to choose a strong password.

Creating a Database​

Now that you’ve installed MySQL, the next step is to create a database. A database is a collection of tables that are used to store data. To create a database in MySQL, you’ll need to use the CREATE DATABASE command. Here’s an example:

CREATE DATABASE mydatabase;

This command will create a database called “mydatabase.” You can replace “mydatabase” with any name you choose for your database.

Tables​

Once you’ve created a database, the next step is to create tables. A table is a collection of data that is organized into rows and columns. Each table in a database should have a unique name.

To create a table in MySQL, you’ll need to use the CREATE TABLE command. Here’s an example:

CREATE TABLE employees (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

This command will create a table called “employees” with columns for id, firstname, lastname, email, and reg_date. The id column is the primary key for the table, which means that each row in the table will have a unique id.

DDL Commands in MySQL​

Data Definition Language (DDL) commands are one of the most important sets of commands in MySQL, as they allow you to create, modify, and delete database objects such as tables, indexes, and views. We will explore some of the most commonly used DDL commands in MySQL.

CREATE​

The CREATE command is used to create new database objects, such as tables, indexes, and views. We already explored this command above.

ALTER​

The ALTER command in MySQL is used to modify the structure of an existing table. This command allows you to add, modify, or delete columns, constraints, indexes, and other elements of the table structure. In this article, we’ll explore some of the most common uses of the ALTER command in MySQL.

Adding a new column​

One of the most common uses of the ALTER command is to add a new column to an existing table. Here’s an example of how to add a new column to a table called “employees”:

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20) NOT NULL;

In this example, we’re adding a new column called “phone” to the “employees” table. The VARCHAR(20) specifies that the column should be a string of up to 20 characters, and the NOT NULL constraint ensures that every row in the table must have a value for this column.

Modifying a column​

The ALTER command can also be used to modify an existing column in a table. Here’s an example of how to change the data type of a column:

ALTER TABLE employees MODIFY COLUMN salary DECIMAL(10,2);

In this example, we’re modifying the “salary” column in the “employees” table to change its data type to DECIMAL(10,2). This means that the column will now store decimal numbers with up to 10 digits, with 2 digits after the decimal point.

Renaming a column​

The ALTER command can also be used to rename an existing column in a table. Here’s an example of how to rename a column:

ALTER TABLE employees
RENAME COLUMN phone TO mobile_phone;

In this example, we’re renaming the “phone” column in the “employees” table to “mobile_phone”. This command will update the column name in the table definition, as well as any references to the column in other parts of the database.

Adding a constraint​

The ALTER command can also be used to add constraints to an existing table. Here’s an example of how to add a foreign key constraint to a table:

ALTER TABLE orders
ADD CONSTRAINT fk_customer_id
FOREIGN KEY (customer_id)
REFERENCES customers(id);

In this example, we’re adding a foreign key constraint to the “orders” table. This constraint ensures that every value in the “customer_id” column of the “orders” table must match a value in the “id” column of the “customers” table.

DROP​

The DROP command is used to delete existing database objects, such as tables and indexes. Here is an example of how to delete an existing table using the DROP command:

DROP TABLE employees;

In this example, we are deleting the employees table. This command will permanently delete the table and all of its data, so use it with caution.

RENAME​

The RENAME command is used to rename existing database objects, such as tables and columns. Here is an example of how to rename an existing table using the RENAME command:

RENAME TABLE employees TO staff;

In this example, we are renaming the employees table to staff. This command will update the table name and any references to the old table name in other database objects.

TRUNCATE​

The TRUNCATE command is used to delete all of the data from an existing table, but leave the table structure intact. Here is an example of how to truncate an existing table using the TRUNCATE command:

TRUNCATE TABLE employees;

In this example, we are deleting all of the data from the employees table, but leaving the table structure intact. This command is useful when you want to remove all of the data from a table without deleting the table itself.

Basic SQL Commands​

Now that you’ve created a database and a table, you can start using SQL commands to add data to the table and retrieve data from the table.

Here are some basic SQL commands that you can use with MySQL:

  1. INSERT – This command is used to add data to a table. Here’s an example:
INSERT INTO employees (firstname, lastname, email)
VALUES ('John', 'Doe', '[email protected]');

This command will add a new row to the “employees” table with the firstname, lastname, and email values specified.

  1. SELECT – This command is used to retrieve data from a table. Here’s an example:
SELECT * FROM employees;

This command will retrieve all of the rows from the “employees” table.

  1. UPDATE – This command is used to update existing data in a table. Here’s an example:
UPDATE employees SET email='[email protected]' WHERE id=1;

This command will update the email value for the row with an id of 1 in the “employees” table.

  1. DELETE – This command is used to delete data from a table. Here’s an example:
DELETE FROM employees WHERE id=1;

This command will delete the row with an id of 1 from the “employees” table.

Advanced SQL Commands​

In addition to the basic SQL commands, MySQL also supports a wide range of advanced SQL commands that can be used to manipulate data in more complex ways. Here are some examples of advanced SQL commands that you may find useful:

  1. JOIN – This command is used to combine data from two or more tables based on a common column. Here’s an example:
SELECT employees.firstname, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

This command will retrieve the firstname column from the “employees” table and the department_name column from the “departments” table, and combine them based on the department_id column that is common to both tables.

  1. GROUP BY – This command is used to group data based on a particular column. Here’s an example:
SELECT department_id, COUNT(*) as num_employees
FROM employees GROUP BY department_id;

This command will group the data in the “employees” table by department_id and count the number of employees in each department.

  1. ORDER BY
SELECT * FROM employees ORDER BY lastname ASC;

This command will retrieve all of the rows from the “employees” table and sort them in ascending order based on the lastname column.

CONSTRAINTS IN MySQL​

In MySQL, constraints are used to define rules that ensure the consistency and integrity of data in a database table. Constraints are applied to one or more columns in a table to limit the values that can be inserted or updated. Lets explore the different types of constraints in MySQL and how they are used.

Primary key constraints​

A primary key constraint is used to ensure that each row in a table has a unique identifier. In MySQL, a primary key constraint is defined using the PRIMARY KEY keyword, followed by the name of the column or columns that make up the primary key.

Here’s an example of how to create a primary key constraint:

CREATE TABLE customers (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   email VARCHAR(50),
   PRIMARY KEY (id)
);

In this example, the id column is defined as the primary key for the customers table. This means that each row in the table will have a unique id value.

Foreign key constraints​

A foreign key constraint is used to enforce a relationship between two tables. A foreign key constraint specifies that the values in a column (or columns) of one table must match the values in the primary key column(s) of another table. In MySQL, a foreign key constraint is defined using the FOREIGN KEY keyword, followed by the name of the column or columns that make up the foreign key.

Here’s an example of how to create a foreign key constraint:

CREATE TABLE orders (
   id INT NOT NULL AUTO_INCREMENT,
   customer_id INT,
   amount DECIMAL(10,2),
   PRIMARY KEY (id),
   FOREIGN KEY (customer_id) REFERENCES customers(id)
);

In this example, the customer_id column in the orders table is defined as a foreign key that references the id column in the customers table. This ensures that every value in the customer_id column of the orders table must match a value in the id column of the customers table.

Unique constraints​

A unique constraint is used to ensure that each value in a column is unique. In MySQL, a unique constraint is defined using the UNIQUE keyword, followed by the name of the column or columns that make up the unique constraint.

Here’s an example of how to create a unique constraint:

CREATE TABLE products (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   sku VARCHAR(20),
   price DECIMAL(10,2),
   PRIMARY KEY (id),
   UNIQUE (sku)
);

In this example, the sku column in the products table is defined as a unique constraint. This means that each value in the sku column must be unique.

Check constraints​

A check constraint is used to ensure that the values in a column meet a specific condition. In MySQL, a check constraint is defined using the CHECK keyword, followed by the condition that must be met.

Here’s an example of how to create a check constraint:

CREATE TABLE employees (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(50),
   salary DECIMAL(10,2),
   PRIMARY KEY (id),
   CHECK (salary >= 0)
);

In this example, the check constraint ensures that the salary column in the employees table must be greater than or equal to 0.

JOINS​

Joins in MySQL are a powerful feature that allow you to combine data from multiple tables into a single result set. This can be useful for a wide range of applications, from simple queries to complex data analysis.

There are several different types of joins available in MySQL, including inner joins, outer joins, and cross joins. Each of these types of joins has its own specific use case and syntax.

Inner Joins​

Inner joins are the most common type of join used in MySQL. An inner join returns only the rows that match the specified join condition. This means that if a row in one table does not have a matching row in the other table, it will not be included in the result set.

To perform an inner join in MySQL, you use the JOIN keyword in your query. Here’s an example:

SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

In this example, we’re selecting the order ID and customer name from two different tables, orders and customers. The join condition is specified using the ON keyword, where we’re matching the customer ID column from the orders table with the customer ID column from the customers table.

Outer Joins​

Outer joins are similar to inner joins, but they include all the rows from one table even if there is no matching row in the other table. This means that if a row in one table does not have a matching row in the other table, it will still be included in the result set with NULL values in the columns from the other table.

There are two types of outer joins in MySQL: left outer joins and right outer joins. A left outer join includes all the rows from the left table and any matching rows from the right table. A right outer join includes all the rows from the right table and any matching rows from the left table.

Here’s an example of a left outer join in MySQL:

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

In this example, we’re selecting the customer name and order ID from two different tables, customers and orders. The join condition is specified using the ON keyword, where we’re matching the customer ID column from the customers table with the customer ID column from the orders table. The LEFT JOIN keyword indicates that we want to include all the rows from the customers table, even if there is no matching row in the orders table.

Cross Joins​

A cross join in MySQL returns the Cartesian product of the two tables being joined. This means that every row from the first table is combined with every row from the second table, resulting in a large number of rows in the result set.

Here’s an example of a cross join in MySQL:

SELECT customers.customer_name, orders.order_id
FROM customers
CROSS JOIN orders;

In this example, we’re selecting the customer name and order ID from two different tables, customers and orders. The CROSS JOIN keyword indicates that we want to combine every row from the customers table with every row from the orders table.

Aggregrate Functions in MySQL​

Aggregate functions in MySQL are a set of built-in functions that are used to perform calculations on groups of rows or columns in a table. These functions can be used to summarize data and generate useful insights that can inform business decisions or inform data analysis.

There are several different aggregate functions available in MySQL, including COUNT, SUM, AVG, MIN, and MAX. Each of these functions can be used to calculate different types of aggregate data, and can be combined with other functions and statements to create more complex queries.

COUNT​

The COUNT function in MySQL is used to count the number of rows in a table that meet a specified condition. Here’s an example:

SELECT COUNT(*) as num_orders
FROM orders
WHERE order_date >= '2022-01-01';

In this example, we’re using the COUNT function to count the number of orders in the orders table that were placed after January 1, 2022. The asterisk (*) is used as a placeholder for all columns in the table, which means that we’re counting all the rows that meet the specified condition.

Mathematical Functions​

SUM​

The SUM function in MySQL is used to calculate the sum of all values in a specified column. Here’s an example:

SELECT SUM(order_total) as total_sales
FROM orders
WHERE order_date >= '2022-01-01';

In this example, we’re using the SUM function to calculate the total sales for all orders in the orders table that were placed after January 1, 2022. The order_total column contains the total amount of each order, so by summing this column we can calculate the total sales for the specified period.

AVG​

The AVG function in MySQL is used to calculate the average value of a specified column. Here’s an example:

SELECT AVG(order_total) as avg_sale
FROM orders
WHERE order_date >= '2022-01-01';

In this example, we’re using the AVG function to calculate the average sale for all orders in the orders table that were placed after January 1, 2022. By dividing the sum of all order totals by the number of orders, we can calculate the average sale amount.

MIN​

The MIN function in MySQL is used to find the smallest value in a specified column. Here’s an example:

SELECT MIN(order_total) as min_sale
FROM orders
WHERE order_date >= '2022-01-01';

In this example, we’re using the MIN function to find the smallest sale amount for all orders in the orders table that were placed after January 1, 2022. This can be useful for identifying outliers or anomalies in the data.

MAX​

The MAX function in MySQL is used to find the largest value in a specified column. Here’s an example:

SELECT MAX(order_total) as max_sale
FROM orders
WHERE order_date >= '2022-01-01';

In this example, we’re using the MAX function to find the largest sale amount for all orders in the orders table that were placed after January 1, 2022. This can be useful for identifying top performers or high-value customers.

Built In Functions in MySQL​

Lets explore some of the most commonly used built-in functions in MySQL.

String Functions​

MySQL provides a variety of string functions that can be used to manipulate text data in a table. Some of the most commonly used string functions include:

  • CONCAT: This function is used to concatenate two or more strings together. For example, CONCAT(“Hello”, “ World”) would return “Hello World”.
  • SUBSTRING: This function is used to extract a substring from a larger string. For example, SUBSTRING(“Hello World”, 1, 5) would return “Hello”.
  • TRIM: This function is used to remove leading or trailing whitespace from a string. For example, TRIM(“ Hello World ”) would return “Hello World”.
  • UPPER/LOWER: These functions are used to convert a string to upper or lower case, respectively. For example, UPPER(“Hello World”) would return “HELLO WORLD”, while LOWER(“Hello World”) would return “hello world”.
  • LENGTH: This function is used to return the length of a string. For example, LENGTH(“Hello World”) would return 11.

Date and Time Functions​

MySQL provides a variety of date and time functions that can be used to manipulate date and time data in a table. Some of the most commonly used date and time functions include:

  • NOW: This function is used to return the current date and time. For example, NOW() would return the current date and time in the format “YYYY-MM-DD HH:MM:SS”.
  • DATE: This function is used to extract the date portion of a datetime value. For example, DATE(“2022-05-01 12:34:56”) would return “2022-05-01”.
  • DATE_FORMAT: This function is used to format a date or datetime value. For example, DATE_FORMAT(“2022-05-01”, "%Y-%m-%d”) would return “2022-05-01”, while DATE_FORMAT(“2022-05-01 12:34:56”, "%H:%i:%s”) would return ‘12:34:56’.

Conditional Functions​

MySQL provides a variety of conditional functions that can be used to perform logic operations on data in a table. Some of the most commonly used conditional functions include:

  • IF: This function is used to perform a conditional check on a value. For example, IF(sales > 1000, “High”, “Low”) would return “High” if the sales value is greater than 1000, and “Low” otherwise.
  • CASE: This function is used to perform a more complex conditional check on a value. For example, CASE WHEN sales > 1000 THEN “High” WHEN sales > 500 THEN “Medium” ELSE “Low” END would return “High” if the sales value is greater than 1000, “Medium” if the sales value is greater than 500 but less than or equal to 1000, and “Low” otherwise.

Sample Problem and Solution​

Question 1​

Image 1

Image 2

Solution​

Here is the MySQL code to create the required tables with suitable constraints and insert data into them:

-- Create the DEPARTMENT table
CREATE TABLE DEPARTMENT (
    DEPT_ID INT NOT NULL,
    DEPT_NAME VARCHAR(50) NOT NULL,
    PRIMARY KEY (DEPT_ID)
);

-- Create the PROJECT table
CREATE TABLE PROJECT (
    PROJECT_ID INT NOT NULL,
    PROJECT_NAME VARCHAR(50) NOT NULL,
    DID INT,
    PRIMARY KEY (PROJECT_ID),
    CONSTRAINT fk_dept_project FOREIGN KEY (DID) REFERENCES DEPARTMENT(DEPT_ID)
);

-- Create the EMPLOYEE table
CREATE TABLE EMPLOYEE (
    EMP_ID INT NOT NULL,
    NAME VARCHAR(50) NOT NULL,
    GENDER CHAR(1),
    DID INT,
    PID INT,
    DOJ DATE,
    AGE INT,
    LOCATION VARCHAR(50) DEFAULT 'CHENNAI',
    PRIMARY KEY (EMP_ID),
    CONSTRAINT fk_dept_employee FOREIGN KEY (DID) REFERENCES DEPARTMENT(DEPT_ID),
    CONSTRAINT fk_project_employee FOREIGN KEY (PID) REFERENCES PROJECT(PROJECT_ID),
    CHECK (AGE > 21)
);

-- Insert data into the DEPARTMENT table
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES
(1, 'Sales'),
(2, 'Marketing'),
(3, 'Finance'),
(4, 'Human Resources'),
(5, 'Information Technology');

-- Insert data into the PROJECT table
INSERT INTO PROJECT (PROJECT_ID, PROJECT_NAME, DID) VALUES
(101, 'Sales Campaign', 1),
(102, 'Product Launch', 2),
(103, 'Financial Analysis', 3),
(104, 'Employee Training', 4),
(105, 'Database Migration', 5);

-- Insert data into the EMPLOYEE table
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, DID, PID, DOJ, AGE, LOCATION) VALUES
(1001, 'John Smith', 'M', 1, 101, '2022-01-01', 25, 'New York'),
(1002, 'Jane Doe', 'F', 2, 102, '2022-02-01', 28, 'London'),
(1003, 'Mike Johnson', 'M', 3, 103, '2022-03-01', 30, 'Sydney'),
(1004, 'Mary Smith', 'F', 4, 104, '2022-04-01', 23, NULL),
(1005, 'Tom Williams', 'M', 5, 105, '2022-05-01', 22, 'Chennai');

-- Demonstrate constraint violations
-- Insert a department with null DEPT\_NAME (should fail)
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (6, NULL);

-- Insert a project with non-existent department (should fail)
INSERT INTO PROJECT (PROJECT_ID, PROJECT_NAME, DID) VALUES (106, 'New Project', 6);

-- Insert an employee with age less than 21 (should fail)
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, DID, PID, DOJ, AGE, LOCATION) VALUES
(1006, 'Amy Johnson', 'F', 2, 102, '2022-06-01', 20, 'Paris');

-- Update an employee with non-existent department (should fail)
UPDATE EMPLOYEE SET DID = 6 WHERE EMP_ID = 1005;

In this code, we have first created the required tables with suitable constraints. The DEPARTMENT table has a primary key constraint on DEPT_ID and a not null constraint on DEPT_NAME.

Question 2​

Image 3

Solution​

Here’s the MySQL code for creating the EMPLOYEE table and solving the given queries:

CREATE TABLE EMPLOYEE (
    EMPID INT PRIMARY KEY,
    EMPNAME VARCHAR(50) NOT NULL,
    JOB VARCHAR(50),
    DOB DATE,
    SALARY DECIMAL(10,2),
    DEPTNO INT,
    GENDER VARCHAR(10)
);

Calculate the square root of the salary of all employees

SELECT EMPNAME, SQRT(SALARY) AS SQUARE_ROOT_SALARY FROM EMPLOYEE;

Apply any other five numeric built in function to ‘salary’ attribute of employee table

SELECT EMPNAME, ABS(SALARY) AS ABS_SALARY, CEILING(SALARY) AS CEILING_SALARY, 
ROUND(SALARY, 1) AS ROUND_SALARY, POWER(SALARY, 2) AS POWER_SALARY, 
EXP(SALARY) AS EXP_SALARY 
FROM EMPLOYEE;

Extract only the first 5 characters of the employee names

SELECT SUBSTRING(EMPNAME, 1, 5) AS FIRST_FIVE_CHARACTERS FROM EMPLOYEE;

Apply any other five string built in function to ‘name’ attribute of employee table

SELECT CONCAT(EMPNAME, ' is a ', JOB) AS CONCAT_EMPLOYEE, 
UPPER(EMPNAME) AS UPPERCASE_EMPLOYEE, 
LOWER(EMPNAME) AS LOWERCASE_EMPLOYEE, 
LENGTH(EMPNAME) AS NAME_LENGTH, 
REPLACE(EMPNAME, ' ', '-') AS REPLACE_EMPLOYEE 
FROM EMPLOYEE;

Determine the max and min salary and rename the column as max_salary and min_salary

SELECT MAX(SALARY) AS max_salary, MIN(SALARY) AS min_salary FROM EMPLOYEE;

Display the month name of date “14-jul-15” in number

SELECT YEAR(DOB), MONTH(DOB), DAY(DOB) FROM EMPLOYEE;

Display the Dob of all employees in the format “dd-mm-yy”

SELECT DATE_FORMAT(DOB, '%d-%m-%y') AS DOB_FORMATTED FROM EMPLOYEE;

Display the date two months after the Dob of employees

SELECT DATE_ADD(DOB, INTERVAL 2 DAY) AS TWO_MONTHS_AFTER_DOB FROM EMPLOYEE;

Display the last date of that month in “05-Oct-15”

SELECT LAST_DAY('2015-10-05') AS LAST_DAY_OF_MONTH;

Display the rounded date in year format, month format, day format

SELECT YEAR(DOB), MONTH(DOB), DAY(DOB) FROM EMPLOYEE;

Display the date 60 days before current date

SELECT DATE_SUB(CURDATE(), INTERVAL 60 DAY);

Display the names and dob of all employees who were born in August

SELECT EMPNAME, DOB FROM EMPLOYEE WHERE MONTH(BIRTHDAY) = 8;

List out the employee names who will celebrate their birthdays during current month

SELECT EMPNAME FROM EMPLOYEE WHERE MONTH(BIRTHDAY) = MONTH(CURDATE());

List all female employees who were born in April

SELECT EMPNAME FROM EMPLOYEE WHERE GENDER = 'F' AND MONTH(BIRTHDAY) = 4;

What is the difference between maximum and minimum salaries of employees in the organization?

SELECT MAX(SALARY) - MIN(SALARY) AS salary_difference FROM EMPLOYEE;

Display number of employees working in each department and their department name

SELECT DEPTNO, COUNT(*) AS num_employees FROM EMPLOYEE GROUP BY DEPTNO;

Display total salary spent for employees:

SELECT SUM(SALARY) AS total_salary FROM EMPLOYEE;

Display total salary spent for each job category:

SELECT JOB, SUM(SALARY) AS total_salary FROM EMPLOYEE GROUP BY JOB;

Display lowest paid employee details under each manager (assuming there is a MANAGERID column in EMPLOYEE table):

SELECT E1.MANAGERID, E1.EMPID, E1.SALARY 
FROM EMPLOYEE E1 
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE E2 WHERE E1.MANAGERID = E2.MANAGERID)
GROUP BY MANAGERID;

Find how many job titles are available in employee table:

SELECT COUNT(DISTINCT JOB) AS num_job_titles FROM EMPLOYEE;

Question 3​

Image 4

Create the following tables and insert the required values.​

member(memb_no, name, age)

book(isbn, title, authors, publisher)

borrowed(memb_no, isbn, date), where (memb_no) = (1, 'John Doe', 25);, (isbn) = ('978-3-16-148410-0', 'Book Title', 'Author Name', 'McGraw-Hill');, (borrowed) = (1, '978-3-16-148410-0', '2022-01-01');

Write the following queries in SQL.

  1. Print the names of members who have borrowed any book published by “McGraw-Hill”.
  2. Print the names of members who have borrowed all books published by “McGraw-Hill”.
  3. For each publisher, print the names of members who have borrowed more than five books of that publisher.
  4. Print the average number of books borrowed per member.

Solution​

Here is the MySQL code to create the member, book, and borrowed tables:

CREATE TABLE `member` (
    memb_no INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

CREATE TABLE `book` (
    isbn VARCHAR(13) PRIMARY KEY,
    title VARCHAR(255),
    authors VARCHAR(255),
    publisher VARCHAR(255)
);

CREATE TABLE `borrowed` (
    memb_no INT,
    isbn VARCHAR(13),
    date DATE,
    FOREIGN KEY (memb_no) REFERENCES `member`(memb_no),
    FOREIGN KEY (isbn) REFERENCES book(isbn)
);

You can insert values into these tables using the INSERT INTO statement. For example:

INSERT INTO `member` (memb_no, name, age) VALUES (1, 'John Doe', 25);
INSERT INTO `book` (isbn, title, authors, publisher) VALUES ('978-3-16-148410-0', 'Honda Civic', 2010);
INSERT INTO `borrowed` (memb_no, isbn, date) VALUES (1, '978-3-16-148410-0', '2022-01-01');

Here are the queries to solve the tasks you mentioned:

  1. SELECT DISTINCT m.name FROM member m JOIN borrowed b ON m.memb_no = b.memb_no JOIN book bk ON bk.isbn = bk.isbn WHERE bk.publisher = 'McGraw-Hill';
  2. Print the names of members who have borrowed all books published by “McGraw-Hill”:SELECT m.name FROM member m WHERE NOT EXISTS ( SELECT * FROM book bk WHERE bk.publisher = 'McGraw-Hill' AND NOT EXISTS ( SELECT * FROM borrowed b WHERE b.memb_no = m.memb_no AND b.isbn = bk.isbn ) );
  3. For each publisher, print the names of members who have borrowed more than five books of that publisher:SELECT bk.publisher, m.name FROM member m JOIN borrowed b ON m.memb_no = b.memb_no JOIN book bk ON bk.isbn = bk.isbn GROUP BY bk.publisher, m.name HAVING COUNT(*) > 5;
  4. Print the average number of books borrowed per member:SELECT AVG(num_books) FROM ( SELECT COUNT(*) AS num_books FROM borrowed GROUP BY memb_no ) subq;

Question 4​

Image 5

Create the following tables and insert the required values.​

person (driver id, name, address)

car (license, model, year)

accident (report number, date, location)

owns (driver id, license)

participated (report number, license, driver id, damage amount)

Write the following queries in SQL.

  1. Find the number of accidents in which the cars belonging to “Kumar” were involved.
  2. Update the damage amount for the car with the license number “TN21HJ1234” in the accident with report number “AR2197” to Rs.12000.
  3. Find the total number of people who owned cars that were involved in accidents in 2009
  4. Display the name and address of the person whose license number is “TN-22-2008-0003369”

Here is the MySQL code to create the person, car, accident, owns, and participated tables:

CREATE TABLE person (
    driver_id INT PRIMARY KEY,
    name VARCHAR(255),
    address VARCHAR(255)
);

CREATE TABLE car (
    license VARCHAR(255) PRIMARY KEY,
    model VARCHAR(255),
    year INT
);

CREATE TABLE accident (
    report_number VARCHAR(255) PRIMARY KEY,
    date DATE,
    location VARCHAR(255)
);

CREATE TABLE owns (
    driver_id INT,
    license VARCHAR(255),
    FOREIGN KEY (driver_id) REFERENCES person(driver_id),
    FOREIGN KEY (license) REFERENCES car(license)
);

CREATE TABLE participated (
    report_number VARCHAR(255),
    license VARCHAR(255),
    driver_id INT,
    damage_amount FLOAT,
    FOREIGN KEY (report_number) REFERENCES accident(report_number),
    FOREIGN KEY (license) REFERENCES car(license),
    FOREIGN KEY (driver_id) REFERENCES person(driver_id)
);

You can insert values into these tables using the INSERT INTO statement. For example:

INSERT INTO person (driver_id, name, address) VALUES (1, 'Kumar', '123 Main St');
INSERT INTO car (license, model, year) VALUES ('TN21HJ1234', 'Honda Civic', 2010);
INSERT INTO accident (report_number, date, location) VALUES ('AR2197', '2009-01-01', 'Chennai');
INSERT INTO owns (driver_id, license) VALUES (1, 'TN21HJ1234');
INSERT INTO participated (report_number, license, driver_id, damage_amount) VALUES ('AR2197', 'TN21HJ1234', 1, 10000);
  1. Find the number of accidents in which the cars belonging to “Kumar” were involved:SELECT COUNT(DISTINCT a.report_number) FROM accident a JOIN participated p ON a.report_number = p.report_number JOIN owns o ON p.license = o.license JOIN person pr ON o.driver_id = pr.driver_id WHERE pr.name = 'Kumar';
  2. Update the damage amount for the car with the license number “TN21HJ1234” in the accident with report number “AR2197” to Rs.12000:UPDATE participated SET damage_amount = 12000 WHERE report_number = 'AR2197' AND license = 'TN21HJ1234';
  3. Find the total number of people who owned cars that were involved in accidents in 2009:SELECT COUNT(DISTINCT o.driver_id) FROM owns o JOIN participated p ON o.license = p.license JOIN accident a ON p.report_number = a.report_number WHERE YEAR(a.date) = 2009;
  4. Display the name and address of the person whose license number is “TN-22-2008-0003369”:SELECT pr.name, pr.address FROM person pr JOIN owns o ON pr.driver_id = o.driver_id WHERE o.license = 'TN-22-2008-0003369';

Conclusion​

MySQL is a powerful database management system that is widely used in the web development industry. By following the steps outlined in this beginner’s guide, you should now have a basic understanding of how to install and use MySQL, create databases and tables, and use SQL commands to manipulate data. However, this is just the beginning – MySQL offers a wide range of advanced features and functionality that can help you to create more complex and sophisticated applications. To continue your learning journey with MySQL, consider exploring online resources, taking courses, and participating in forums and communities where you can connect with other developers and learn from their experiences.