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.
- Fork this repo.
- Clone this repo.
- 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.
- In the repository, create a Java project and add the code for the following prompts.
Once you finish the assignment, submit a URL link to your repository or your pull request in the field below.
- 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 |
- 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 |
- In the Airline database write the SQL script to get the total number of flights in the database.
- In the Airline database write the SQL script to get the average flight distance.
- In the Airline database write the SQL script to get the average number of seats.
- In the Airline database write the SQL script to get the average number of miles flown by customers grouped by status.
- In the Airline database write the SQL script to get the maximum number of miles flown by customers grouped by status.
- In the Airline database write the SQL script to get the total number of aircraft with a name containing Boeing.
- In the Airline database write the SQL script to find all flights with a distance between 300 and 2000 miles.
- In the Airline database write the SQL script to find the average flight distance booked grouped by customer status (this should require a join).
- In the Airline database write the SQL script to find the most often booked aircraft by gold status members (this should require a join).
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:
- 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
);
- 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?
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;
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;
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:
- Check your internet connection: Ensure that your internet connection is stable and working.
- Verify your repository URL: Make sure that you are using the correct repository URL to push your changes.
- 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
- 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
- Check for conflicts: If there are any conflicts between your local repository and the remote repository, resolve them before pushing changes.
- 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>