CREATE TABLE customer (
id INTEGER PRIMARY KEY,
customer_name TEXT,
contact_number TEXT
);
CREATE TABLE supplier (
id INTEGER PRIMARY KEY,
supplier_name TEXT UNIQUE,
contact_number TEXT
);
CREATE TABLE product (
id INTEGER PRIMARY KEY,
supplier_id INTEGER REFERENCES supplier(id),
product_name TEXT,
product_price INTEGER,
UNIQUE(supplier_id, product_name)
);
CREATE TABLE purchase (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customer(id),
purchase_date REAL,
store_id INTEGER REFERENCES store(id)
);
CREATE TABLE store (
id INTEGER PRIMARY KEY,
store_name TEXT,
store_address TEXT
);
CREATE TABLE purchase_product (
purchase_id INTEGER REFERENCES purchase(id),
product_id INTEGER REFERENCES product(id),
quantity INTEGER
);
List all ladders with multiple suppliers, along with the supplier that offers the lowest price for the given product.
List the names of all customers who have purchased a product that could have been purchased from a supplier with a lower price by at least 5 dollars
(500 cents), ordered by customer_name
List all customers who have never made a purchase at the Diamond Drive
List the names of all customers who purchased Tool 1717 and then later purchased O-Ring 1736, ordered by customer name.
List the name and number of purchases made at each store, sorted by store name.
List the name and total number of (non-distinct) items purchased at each store, sorted by store name.
List the name and total number of (non-distinct) items purchased at each store, sorted by store name, after or on 2018-11-30.
List the name and total number of (non-distinct) items purchased at each store, sorted by store name, for the months of October (before 2018-11-01), November (between 2018-11-01 and 2018-12-01), and December (after 2018-12-01). Your output should look like this, with ??? replaced with correct values:
Los Alamos DD|October|??? Los Alamos DD|November|??? Los Alamos DD|December|??? Los Alamos TD|October|??? Los Alamos TD|November|??? Los Alamos TD|December|??? White Rock|October|??? White Rock|November|??? White Rock|December|???
List all ladders with multiple suppliers, along with the
supplier that offers the lowest price for the given product.
Ans: Select supplier_name from supplier s join product p ON s.id =
p.supplier_id where p.product_name ="xyz" order By p.product_price
ASC
List all customers who have never made a purchase at the Diamond
Drive
Ans: select c.customer_name from customer c JOIN purchase p on
p.customer_id = c.id JOIN store s on s.id = p.store_id where
s.store_name !="Diamond Drive"
List the name and number of purchases made at
each store, sorted by store name.
ANS: Select s.store_name,count(p.id) from store s LEFT
OUTER JOIN purchase p ON s.id = p.store_id GROUP BY s.id ORDER BY
s.store_name ASC
List the name and total number of (non-distinct)
items purchased at each store, sorted by store name.
ANS: please provide more info
List the name and total number of
(non-distinct) items purchased at each store, sorted by store name,
after or on 2018-11-30.
ANS: please provide more info
List the name and total number of
(non-distinct) items purchased at each store, sorted by store name,
for the months of October (before 2018-11-01), November (between
2018-11-01 and 2018-12-01), and December (after 2018-12-01). Your
output should look like this, with ??? replaced with correct
values:
ANS: please provide more info
CREATE TABLE customer ( id INTEGER PRIMARY KEY, customer_name TEXT, contact_number TEXT );...
Consider the following table definitions: create table node (node_id integer primary key, node_color varchar(10)); create table edge (edge_id integer primary key, origin_id integer, destination_id integer, foreign key (origin_id) references node (node_id), foreign key (destination_id) references node (node_id) ); Which of the following list the ids of nodes that link to both the node with id 3 and and the node with id 4? select node id from node inner join edge on node_id=origin_id where destination_id=3 and destination_id=4; select node_id from...
The database(or the tables) are follow: CREATE TABLE employees ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL, salary REAL NOT NULL DEFAULT 25000.0 ); CREATE TABLE employee_audit_log ( employee_id INTEGER NOT NULL, occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); INSERT INTO employees(name, salary) VALUES ('Arnold Schwarznegger', 35000), ('Yuri Gargarin', 27000), ('Anakin Skywalker', 450000), ('Said Faroghi', 15000), ('Zino Holwerda', 8500); 1. Create a trigger for the employees table so that a new row is inserted in employee audit_log...
create table candidate ( cand_id varchar(12) primary key, -- cand_id name varchar(40) -- cand_nm ); create table contributor ( contbr_id integer primary key, name varchar(40), -- contbr_nm city varchar(40), -- contbr_city state varchar(40), -- contbr_st zip varchar(20), -- contbr_zip employer varchar(60), -- contbr_employer occupation varchar(40) -- contbr_occupation ); create table contribution ( contb_id integer primary key, cand_id varchar(12), --...
SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address has been denormalized to include City and State for performance reasons. DeptNbr is a foreign key that references DeptNbr in the Department table Manager is a foreign key that references EmployeeID in the Employees table Data Structure: (EmployeeId, LastName, FirstName, Street, City, State, Zip, DateOfHire, DateOfBirth, JobTitle, Salary, DeptNbr(fk), Manager(fk)) TABLE: Department Business Rules: DeptNbr is defined as the primary key. Data Structure: (DeptNbr,...
Database Concepts! please help Consider the following create table statements: create table R (a integer primary key); create tables (b integer primary key, c integer references R(a) on update cascade); Assume that these tables have the following data inserted: R: (1) and (2) S: (1,1) and (2, 1) What happens when we want to delete the record (1) from R? The corresponding record from S and T will be deleted due to the cascade option Only the record in Rwill...
CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK'); INSERT INTO DEPT VALUES (20,'HOME','DALLAS'); INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO'); INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, SAL FLOAT, COMM FLOAT, DEPTNO INTEGER NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), FOREIGN KEY (MGR) REFERENCES EMP(EMPNO), PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, 5000,NULL,10); INSERT INTO...
Based on the CREATE TABLE statements, make an ER model of the database. Give suitable names to the relationships. (Remember cardinality and participation constraints.) The diagram must use either the notation used in the textbook (and the lectures) or the crow’s foot notation. To save you some time: There are a few tables that include the following address fields: Address, City, State, Country and PostalCode (and the same fields with the prefix Billing-). You are allowed to replace these attributes...
(3.5 Marks) Create the table CAR and create the fields with primary key Field Name Data type Size Constraint A2.1 Name Model Price MED Varchar2 25 Not nul Number 10 Primary key Number 10,2 Date Write a SQL Query to display all car details from the "CAR" table and display the Name in lowercase in a new column with the column name as Newname. (3.5 Marks) A2.2 Name Model Price MFD Mazda 456 7760.2523-12-2018 Kia 127 4050.1 30-11-2019 Toyota 443...
Using the Table and data below, create a procedure that accepts product ID as a parameter and returns the name of the product from ProductTable table. Add exception handling to catch if product ID is not in the table. Please use Oracle SQL and provide screenshot. Thanks! CREATE TABLE ProductTable( ProductID INTEGER NOT NULL primary key, ProductName VARCHAR(50) NOT NULL, ListPrice NUMBER(10,2), Category INTEGER NOT NULL ); / INSERT INTO ProductTable VALUES(299,'Chest',99.99,10); INSERT INTO ProductTable VALUES(300,'Wave Cruiser',49.99,11); INSERT INTO ProductTable...
-- Schema definition create table Customer ( cid smallint not null, name varchar(20), city varchar(15), constraint customer_pk primary key (cid) ); create table Club ( club varchar(15) not null, desc varchar(50), constraint club_pk primary key (club) ); create table Member ( club varchar(15) not null, cid smallint not null, constraint member_pk primary key (club, cid), constraint mem_fk_club foreign key (club) references Club, constraint mem_fk_cust...