logo_ironhack_blue 7

LAB Java | SQL

Introduction

We have just learned how to normalize database as well as how to create database schema and how to perform complex SQL queries so let's practice a bit more.


Requirements

  1. Fork this repo.
  2. Clone this repo.
  3. Add your instructor and the class graders as collaborators to your repository. If you are unsure who your class graders are, ask your instructor or refer to the day 1 slide deck.
  4. In the repository, create a Java project and add the code for the following prompts.

Submission

Once you finish the assignment, submit a URL link to your repository or your pull request in the field below.


Instructions

  1. Normalize the following blog database and write the DDL scripts to create the database tables:
author title word count views
Maria Charlotte Best Paint Colors 814 14
Juan Perez Small Space Decorating Tips 1146 221
Maria Charlotte Hot Accessories 986 105
Maria Charlotte Mixing Textures 765 22
Juan Perez Kitchen Refresh 1242 307
Maria Charlotte Homemade Art Hacks 1002 193
Gemma Alcocer Refinishing Wood Floors 1571 7542

  1. Normalize the following airline database and write the DDL scripts to create the database tables:
Customer Name Customer Status Flight Number Aircraft Total Aircraft Seats Flight Mileage Total Customer Mileage
Agustine Riviera Silver DL143 Boeing 747 400 135 115235
Agustine Riviera Silver DL122 Airbus A330 236 4370 115235
Alaina Sepulvida None DL122 Airbus A330 236 4370 6008
Agustine Riviera Silver DL143 Boeing 747 400 135 115235
Tom Jones Gold DL122 Airbus A330 236 4370 205767
Tom Jones Gold DL53 Boeing 777 264 2078 205767
Agustine Riviera Silver DL143 Boeing 747 400 135 115235
Sam Rio None DL143 Boeing 747 400 135 2653
Agustine Riviera Silver DL143 Boeing 747 400 135 115235
Tom Jones Gold DL222 Boeing 777 264 1765 205767
Jessica James Silver DL143 Boeing 747 400 135 127656
Sam Rio None DL143 Boeing 747 400 135 2653
Ana Janco Silver DL222 Boeing 777 264 1765 136773
Jennifer Cortez Gold DL222 Boeing 777 264 1765 300582
Jessica James Silver DL122 Airbus A330 236 4370 127656
Sam Rio None DL37 Boeing 747 400 531 2653
Christian Janco Silver DL222 Boeing 777 264 1765 14642

  1. In the Airline database write the SQL script to get the total number of flights in the database.
  2. In the Airline database write the SQL script to get the average flight distance.
  3. In the Airline database write the SQL script to get the average number of seats.
  4. In the Airline database write the SQL script to get the average number of miles flown by customers grouped by status.
  5. In the Airline database write the SQL script to get the maximum number of miles flown by customers grouped by status.
  6. In the Airline database write the SQL script to get the total number of aircraft with a name containing Boeing.
  7. In the Airline database write the SQL script to find all flights with a distance between 300 and 2000 miles.
  8. In the Airline database write the SQL script to find the average flight distance booked grouped by customer status (this should require a join).
  9. In the Airline database write the SQL script to find the most often booked aircraft by gold status members (this should require a join).

FAQs


I am stuck and don't know how to solve the problem or where to start. What should I do?


If you are stuck in your code and don't know how to solve the problem or where to start, you should take a step back and try to form a clear, straight forward question about the specific issue you are facing. The process you will go through while trying to define this question, will help you narrow down the problem and come up with potential solutions.

For example, are you facing a problem because you don't understand the concept or are you receiving an error message that you don't know how to fix? It is usually helpful to try to state the problem as clearly as possible, including any error messages you are receiving. This can help you communicate the issue to others and potentially get help from classmates or online resources.

Once you have a clear understanding of the problem, you should be able to start working toward the solution.


How to Create a Table and Insert Data into it in SQL?


Creating a table and inserting data into it are the two most basic tasks you will perform in SQL. Here's how you can do it:

  1. Creating a Table

To create a table, you need to use the CREATE TABLE statement. Here's an example of how you can create a table named "employees":

CREATE TABLE employees (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  salary DECIMAL(10, 2) NOT NULL
);
  1. Inserting Data into a Table

Once you have created a table, you can insert data into it. To insert data, you use the INSERT INTO statement. Here's an example of how you can insert data into the "employees" table:

INSERT INTO employees (name, age, salary)
VALUES ('John Doe', 32, 50000),
      ('Jane Doe', 28, 45000),
      ('Jim Smith', 40, 55000);

The above code inserts three rows into the "employees" table. You can insert as many rows as you like, just separate each row by a comma.


What is the difference between using "INNER JOIN", "LEFT JOIN", "RIGHT JOIN" and "FULL OUTER JOIN" in SQL?


An INNER JOIN returns only the rows that have matching values in both tables. It returns only the common values.

A LEFT JOIN returns all the rows from the left table and matching values from the right table. If there is no match, it returns NULL values.

A RIGHT JOIN returns all the rows from the right table and matching values from the left table. If there is no match, it returns NULL values.

A FULL OUTER JOIN returns all the rows from both tables, including matching and non-matching values. If there is no match, it returns NULL values.

Code Snippets:

INNER JOIN:

SELECT *
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

LEFT JOIN:

SELECT *
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

RIGHT JOIN:

SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FULL OUTER JOIN:

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;


What is the purpose of using "GROUP BY", "ORDER BY" and "LIMIT" in SQL?


  1. GROUP BY in SQL is used to group together rows that have the same values in specified columns. This is useful when you want to perform aggregate functions (such as SUM, AVG, MIN, MAX) on each group, rather than on the entire result set. For example, if you have a table of sales transactions and you want to see the total sales for each salesperson, you would use the following SQL code:
SELECT salesperson, SUM(sales)
FROM sales_table
GROUP BY salesperson;
  1. ORDER BY in SQL is used to sort the result set in ascending or descending order based on one or more columns. For example, if you have a table of customers and you want to see the list sorted by last name, you would use the following SQL code:
SELECT *
FROM customers
ORDER BY last_name;
  1. LIMIT in SQL is used to specify the maximum number of rows to be returned to the result set. For example, if you only want to see the first 10 customers in the list, you would use the following SQL code:
SELECT *
FROM customers
LIMIT 10;

By combining these statements, you can produce sophisticated queries that can meet a wide range of needs.



I am unable to push changes to my repository. What should I do?


If you are unable to push changes to your repository, here are a few steps that you can follow:

  1. Check your internet connection: Ensure that your internet connection is stable and working.
  2. Verify your repository URL: Make sure that you are using the correct repository URL to push your changes.
  3. Check Git credentials: Ensure that your Git credentials are up-to-date and correct. You can check your credentials using the following command:
git config --list
  1. Update your local repository: Before pushing changes, make sure that your local repository is up-to-date with the remote repository. You can update your local repository using the following command:
git fetch origin
  1. Check for conflicts: If there are any conflicts between your local repository and the remote repository, resolve them before pushing changes.
  2. Push changes: Once you have resolved any conflicts and updated your local repository, you can try pushing changes again using the following command:
git push origin <branch_name>