Turn in TWO original queries using MySQL and the output from the queries with the follow requirements:
1) two or more joined tables
2) 6 or more attributes
3) Nested select OR having OR group by
Let's look at a selection from the "Orders" table:
OrderID | CustomerID | OrderDate |
---|---|---|
10308 | 2 | 1996-09-18 |
10309 | 37 | 1996-09-19 |
10310 | 77 | 1996-09-20 |
Then, look at a selection from the "Customers" table:
CustomerID | CustomerName | ContactName | Country |
---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mexico |
1) QUERY:
SELECT Orders.OrderID, Customers.CustomerName,
Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
OUTPUT:
OrderID | CustomerName | OrderDate |
---|---|---|
10308 | Ana Trujillo Emparedados y helados | 9/18/1996 |
10365 | Antonio Moreno Taquería | 11/27/1996 |
10383 | Around the Horn | 12/16/1996 |
10355 | Around the Horn | 11/15/1996 |
10278 | Berglunds snabbköp | 8/12/1996 |
2) QUERY:
SELECT CustomerID, CustomerName, ContactName, Address, City, Country FROM Customers;
OUTPUT:
CustomerID | CustomerName | ContactName | Address | City | Country |
---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | Mexico |
4 | Around the Horn | Thomas Hardy | 120 Hanover Sq. | London | UK |
5 | Berglunds snabbköp | Christina Berglund | Berguvsvägen 8 | Luleå | Sweden |
6 | Blauer See Delikatessen | Hanna Moos | Forsterstr. 57 | Mannheim | Germany |
7 | Blondel père et fils | Frédérique Citeaux | 24, place Kléber | Strasbourg | France |
8 | Bólido Comidas preparadas | Martín Sommer | C/ Araquil, 67 | Madrid | Spain |
9 | Bon app' | Laurence Lebihans | 12, rue des Bouchers | Marseille | France |
10 | Bottom-Dollar Marketse | Elizabeth Lincoln | 23 Tsawassen Blvd. | Tsawassen | Canada |
3)
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 35 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Now, let us check the following subquery with a SELECT statement.
SQL> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500) ;
This would produce the following result.
+----+----------+-----+---------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+---------+----------+ | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+---------+----------+
Turn in TWO original queries using MySQL and the output from the queries with the follow...
Having trouble writing two mysql queries for this schema The low level conceptual model of the restaurant database is provided below: DATABASE: RESTAURANT (res_name[pk], City, Phone, Owner_SSN[pk,fk]) FOOD (food_name[pk], Type, Country, Proof) CUSTOMER (SSN[pk], Name, Birth_year, Fav_food[fk], Gender) LIKES (SSN[pk,fk], food[pk,fk]) SERVES (res[fk], foods[fk]) OWNER (SSN[pk], Name, City, Address, Phone) QUERIES 14) Find the total number of foods that are liked by customers. Note that if a food is repeatedly liked by several people, it is just counted as once....
Using Figure 2. show what would be displayed by the
following SQL queries should they be executed against
the tables having content (tuples/rows) shown in Figure 2. In each
answer to a question, do not forget to display column headings in
addition to the tuples/rows that would be shown.
1. SELECT pNo, MAXIat), (SELECT MAX(t) FROM sp WHERE pNo IN (SELECT RNR FROM p WHERE RNam晨= 'cam') )AS MAXcao FROM GROUP BY HAVING MAXIqty)> ALL (SELECT aty FROM Sp WHERE...
True or False for mysql and mongodb: 1. Building a B-tree on one of a tables attributes will improve the query time for all queries. 2. B-trees have a branching factor of 2 or 4. 3. B-tree insertion algorithms move data to a sibling node if the node inserted into is full. 4. Mongo provides everything relational databases do, but insertions are much slower. 5. If one maps from a well-designed ER diagram to the relational model, the database may...
I need help with the following problem: Using the classicmodels database you installed from Module 1, provide an example query using both a group by clause and a having clause. Show no more than ten rows of your query result. Discuss if the query you wrote can be rewritten without those clauses. I have come up with the following for the first part of the question: mysql> select offices.city, count(employees.employeenumber) as NumberOfEmployees from Employees -> inner join offices on offices.officecode...
Create a series of ER Diagrams using MySQL Workbench. Each question below is a different version of the same database, with each version increasing in complexity. You need to submit an ER diagram for each version, in pdf or png format. The easiest would be to export your diagrams as png format and insert them into an MS Word document. For each ER diagram clearly indicate primary keys, foreign keys, and relationships, including cardinality (i.e. 1-to-many). Indicate in your diagram...
Transcribe the following SQL queries into an equivalent relational algebra expression. 1. SELECT s_number , AVG( grade ) FROM grades GROUP BY s_number HAVING AVG( grade ) > 5 . 5 ; 2. SELECT SUM( volume ) FROM fertilizer_applications LEFT JOIN crop_field ON fertilizer_applications . crop_field_id = crop_field . id GROUP BY field_number HAVING field_number = 3 ;
Use MySQL Workbench to do the two task, for each task, run and then take a screenshot. Task 1: Example: You want to keep track of all the customers that live in area 615 and are issued more than 1 invoice. It has a long query!! How about putting it in the view. CREATE VIEW CUSTOMERS_OF_615 AS SELECT CUSTOMER.CUS_CODE, COUNT(INV_NUMBER) AS INVOICE_COUNT FROM CUSTOMER JOIN INVOICE USING (CUS_CODE) WHERE CUSTOMER.CUS_AREACODE = 615 GROUP BY CUSTOMER.CUS_CODE HAVING INVOICE_COUNT >1 A VIEW...
Use MySQL Workbench to create a simple ER model and use the Forward Engineering functionality of Workbench to create a simple DB (specs follow). This focuses on how MySQL Workbench is used to create a model and then a DB from it, rather than on ER modeling. The sample model you are to create is on books, authors, and publishers - a model that could be part of a larger ER model for a library. A book has an ISBN...
You will develop an E-Commerce database used to maintain
customers, products and sales information. You are required to 1)
gather and analyze requirements 2) design logical structure of the
database 3) create stored procedures to develop the tables and
insert the data 4) write SQL statements for data extraction and
reporting.
Throughout the course of this semester you have analyzed the
requirements for an eCommerce database, designed and developed your
database. As a class we have gone through the process...
Using MySQL Workbench:
Submit the printouts of your interaction with the DBMS. ID name ID delivery_timestamp supplier supplied item city name quantity 1. [30-point] Create an SQL database of the above ER-diagram using "create table” statements. The type of attribute "quantity" is integer, "delivery_timestamp" is timestamp, and, for all the others, the type is varchar (up to 100 chars). 2. [10-point] Insert 4 records into supplier, 4 records into item, and 10 records into supplied. 3. [60-points] Perform the following...