Queries and outputs:
Q1)CREATING DATABASE...........
mysql> create database supplier;
Query OK, 1 row affected (2.86 sec)
mysql> use supplier;
Database changed
mysql> create table supplier(id varchar(100),name
varchar(100),city varchar(100));
Query OK, 0 rows affected (2.43 sec)
mysql> alter table supplier add primary key(id);
Query OK, 0 rows affected (2.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table item(id varchar(100) primary key,name
varchar(100));
Query OK, 0 rows affected (0.68 sec)
mysql> create table supplied(quantity int,delivery_timestamp
timestamp,s_id varchar(100),foreign key(s_id) references
supplier(id),i_id varchar(100),foreign key(i_id) references
item(id));
Query OK, 0 rows affected (0.93 sec)
mysql> desc supplier;
+-------+--------------+------+-----+---------+-------+
| Field | Type |
Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(100) | NO | PRI |
NULL |
|
| name | varchar(100) | YES | |
NULL |
|
| city | varchar(100) | YES | |
NULL |
|
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.27 sec)
mysql> desc item;
+-------+--------------+------+-----+---------+-------+
| Field | Type |
Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | varchar(100) | NO | PRI |
NULL |
|
| name | varchar(100) | YES | |
NULL |
|
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> desc supplied;
+--------------------+--------------+------+-----+---------+-------+
|
Field
| Type | Null | Key
| Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
|
quantity
| int(11) | YES
| | NULL
| |
| delivery_timestamp | timestamp | YES
| | NULL
| |
|
s_id
| varchar(100) | YES | MUL | NULL
| |
|
i_id
| varchar(100) | YES | MUL | NULL
| |
+--------------------+--------------+------+-----+---------+-------+
4 rows in set (0.07 sec)
Q2)INSERTING VALUES INTO SUPPLIER TABLE(4 RECORDS).........
mysql> insert into supplier values("s1","raj","vizag");
Query OK, 1 row affected (0.15 sec)
mysql> insert into supplier values("s2","ram","delhi");
Query OK, 1 row affected (0.13 sec)
mysql> insert into supplier
values("s3","hari","mumbai");
Query OK, 1 row affected (0.07 sec)
mysql> insert into supplier values("s4","rajesh","new
york");
Query OK, 1 row affected (0.10 sec)
mysql> select * from supplier;
+----+--------+----------+
| id | name | city |
+----+--------+----------+
| s1 | raj | vizag |
| s2 | ram | delhi |
| s3 | hari | mumbai |
| s4 | rajesh | new york |
+----+--------+----------+
4 rows in set (0.00 sec)
INSERTING VALUES INTO ITEM TABLE(5 RECORDS)......
mysql> insert into item values("i1","electronic");
Query OK, 1 row affected (0.12 sec)
mysql> insert into item values("i2","toy");
Query OK, 1 row affected (0.16 sec)
mysql> insert into item values("i3","home appliances");
Query OK, 1 row affected (0.07 sec)
mysql> insert into item values("i4","costume");
Query OK, 1 row affected (0.13 sec)
mysql> insert into item values("i5","Bolt");
Query OK, 1 row affected (0.07 sec)
mysql> select * from item;
+----+-----------------+
| id |
name
|
+----+-----------------+
| i1 | electronic |
| i2 |
toy
|
| i3 | home appliances |
| i4 | costume
|
| i5 |
Bolt
|
+----+-----------------+
5 rows in set (0.00 sec)
INSERTING VALUES INTO SUPPLIED TABLE(11 RECORDS)..........
mysql> insert into supplied values(50,'2001-01-01
00:00:01','s4','i3');
Query OK, 1 row affected (0.09 sec)
mysql> insert into supplied values(56,'2003-01-01
00:01:01','s3','i1');
Query OK, 1 row affected (0.19 sec)
mysql> insert into supplied values(40,'2003-10-01
00:10:01','s2','i3');
Query OK, 1 row affected (0.09 sec)
mysql> insert into supplied values(45,'2003-10-01
00:10:55','s4','i3');
Query OK, 1 row affected (0.11 sec)
mysql> insert into supplied values(25,'2020-09-01
00:06:55','s1','i1');
Query OK, 1 row affected (0.09 sec)
mysql> insert into supplied values(20,'2010-07-01
00:04:10','s2','i2');
Query OK, 1 row affected (0.14 sec)
mysql> insert into supplied values(20,'2010-07-01
00:04:10','s1','i4');
Query OK, 1 row affected (0.09 sec)
mysql> insert into supplied values(43,'2018-05-01
00:03:10','s1','i2');
Query OK, 1 row affected (0.14 sec)
mysql> insert into supplied values(35,'2008-02-29
00:06:12','s3','i4');
Query OK, 1 row affected (0.09 sec)
mysql> insert into supplied values(35,'2008-03-31
00:06:12','s3','i4');
Query OK, 1 row affected (0.09 sec)
mysql> insert into supplied values(35,'2008-03-31
00:06:12','s3','i5');
Query OK, 1 row affected (0.16 sec)
mysql> SELECT * FROM SUPPLIED;
+----------+---------------------+------+------+
| quantity | delivery_timestamp | s_id | i_id |
+----------+---------------------+------+------+
| 50 | 2001-01-01 00:00:01 |
s4 | i3 |
| 56 | 2003-01-01 00:01:01 |
s3 | i1 |
| 40 | 2003-10-01 00:10:01 |
s2 | i3 |
| 45 | 2003-10-01 00:10:55 |
s4 | i3 |
| 25 | 2020-09-01 00:06:55 |
s1 | i1 |
| 20 | 2010-07-01 00:04:10 |
s2 | i2 |
| 20 | 2010-07-01 00:04:10 |
s1 | i4 |
| 43 | 2018-05-01 00:03:10 |
s1 | i2 |
| 35 | 2008-02-29 00:06:12 |
s3 | i4 |
| 35 | 2008-03-31 00:06:12 |
s3 | i4 |
| 35 | 2008-03-31 00:06:12 |
s3 | i5 |
+----------+---------------------+------+------+
11 rows in set (0.00 sec)
3)A) LIST SUPPLIERS(ID,NAME) WHO SUPPLY ITEM "BOLT"..............
mysql> select supplier.id,supplier.name from
item,supplier,supplied where item.name="bolt" and item.id=i_id and
s_id=supplier.id;
+----+------+
| id | name |
+----+------+
| s3 | hari |
+----+------+
1 row in set (0.07 sec)
B)LIST ALL
ITEMS(ID,NAME) SUPPLIED BY BOTH(IN THIS DATABASE THERE ARE NO ITEMS
WHICH SATISFIES THE CONSTRAINT IF THERE EXISTS THIS QUERY WILL GIVE
A OUTPUT TABLE)
mysql> select item.id,item.name from supplier,item,supplied
where supplier.id="s2" AND supplier.id="s4" and supplier.id=s_id
and i_id=item.id;
Empty set (0.00 sec)
C)FOR EACH SUPPLIER THET SUPPLIES AT LEAST ONR ITEM,LIST IT'S ID,NAME AND THE TOTAL QUANTITY SUPPLIED BY THE SUPPLIER.......
I DID'N GET THAT YOU ARE ASKING FOR ITEM'S OR SUPPLIER'S ID,NAME? SO I AM GIVING YOU BOTH QUERIES
THIS QUERY IS TO LIST ITEM'S NAME AND ID
mysql> select item.id,item.name,sum(quantity) as Total_Quantity
from supplier,item,supplied where supplier.id=s_id and i_id=item.id
group by supplier.id having count(item.id>0);
+----+-----------------+----------------+
| id |
name
| Total_Quantity |
+----+-----------------+----------------+
| i1 | electronic
|
88 |
| i3 | home appliances
|
60 |
| i1 | electronic
|
161 |
| i3 | home appliances
|
95 |
+----+-----------------+----------------+
4 rows in set (0.03 sec)
THIS QUERY IS TO LIST SUPPLIER'S
NAME,ID....
mysql> select supplier.id,supplier.name,sum(quantity) as
Total_Quantity from supplier,item,supplied where supplier.id=s_id
and i_id=item.id group by supplier.id having
count(item.id>0);
+----+--------+----------------+
| id | name | Total_Quantity |
+----+--------+----------------+
| s1 | raj
|
88 |
| s2 | ram
|
60 |
| s3 | hari
|
161 |
| s4 | rajesh
|
95 |
+----+--------+----------------+
4 rows in set (0.00 sec)
NOTE:IF YOU FIND ANY DIFFICULTY IN UNDERSTANDING IT OR IF NEED ANY EXPLANATION OR MODIFICATION PLEASE COMMENT.IF YOU ARE SATISFIED WITH MY ANSWER PLEASE UPVOTE.THANK YOU....
Using MySQL Workbench: Submit the printouts of your interaction with the DBMS. ID name ID delivery_timestamp...
use workbench please Task 1: Create a database and name it practical2DB. (2 marks) Task 2: Create all the four tables according to the relational scheme in Figure 1. (8 marks) Task 3: Insert 5 records to into each table. (5 marks) Task 4: Write a query to list alphabetically ordered names, addresses, and IDs of all the customers whose postcode is in (40150, 40400, 47500). (10 marks) Task 5: Delete 1 record from Products table using their primary keys....
user_id name AppUser birthday owns ISA 7 supervises album_id phone worker name Album Customer Admin supervisor pic creation_date contains places manages image_id order_id Image Print Order filename status amount description contains item_id (product_id IC price quantity Item prints-on Product i subtotal prints Relational table schema (with data type) AppUser (user id: Int, name: VARCHAR(80)) Foreign keys: none Customer (user id: Int, birthday: DATE) Foreign keys: user_id refers AppUser CustomerPhone (user id: INT, phone: VARCHAR(20)) Foreign keys: user_id refers Customer Admin...
IN THE PREVIOUS CHAPTER WE MADE A DATABASE USING OUR LAST NAME AS ITS NAME. QUESTION 1 STATES THIS. PLEASE TYPE WHAT NEEDS TO BE TYPED FOR ALL STEPS. THIS USES CODIO a. 1. Connect to the database you created and named in Module One (for example, Jetson). Type after the prompt mysql> a. use (table you named); i. Example: mysql> use Jetson; 2. Create the Employee table using the SQL statement shown here. Press Return after each line. CREATE...
Prepare and execute each of the queries listed using the "Adventureworks2012" database in SQL: Server: http://msftdbprodsamples.codeplex.com/releases/view/93587 When all of your queries are complete, cut and paste the SQL Syntax into a word document. In order to see what the column names are, you need to click on the table and then Columns to see the field names. Make sure to include column headings that make sense in the queries (use the as “Field Name” after the field selected). Multi-table Queries...
Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it to create tables and populate with the supplied data. If there are any errors in the script fix them. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module. Then answer all questions. Write the answer below each question then take screen shot of the result. This script builds...
Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting, and employee data, and these different data sets must all be efficiently managed in order to make the data accessible. Companies rely on database engineers to ensure that their records are accurate, updated, and tracked in real time. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final...
This is a website picture The html and css code is as follow And my problem is as follow use the php and mysql Northwind Customer Database Search List Records All Records CustomerName v CustomerName ContactName Address City PostalCode Search O ASC DESC List All Records Insert Record Country List City CustomerName ContactName Address City PostalCode Country List City Find by IID Find Insert 1 <! DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http:L 3-<head> 4 Kmeta http-equiv-"Content-Type" content-"text/html; charset-utf-8" />...
Using mySQL, tables at the end... This assignment uses the tables from the vets database. The goal of the assignment is to get you to think about joins- inner join and outer joins. If you do a join, where you have the names of two or more tables in the From clause, then you must use the condition join syntax or the column name join. Take care that you do not accidentally do a Cartesian product. If your result set...
Instructions Try to answer all the questions using what you have learned in class. Please make your query general not data related This schema is used for inventory management for an OEM Part Inventory p_id Name Cost Supplier Location 1 Traction motor 200 Melco Japan 2 Alternator 400 kato USA 3 HVAC 300 Melco Japan p_id Warehouse_id quantity 1 A1 100 2 A2 250 3 B1 300 Customer Model c_id Name Location CN Canada National Canada UP Union Pacific USA...
If possible please just send the SQL statement. Thank you Each question within deliverable 3 must begin on a new page and be sure to document the question as the title of each item at the top of each page. Also, using a 12-point font, include the SQL statement and then provide a screen shot of each query. The screen shots must include both the SQL statement and the results for each item below based on the data entered in...