Question

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

);

  1. List all ladders with multiple suppliers, along with the supplier that offers the lowest price for the given product.

  2. 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

  3. List all customers who have never made a purchase at the Diamond Drive

  4. List the names of all customers who purchased Tool 1717 and then later purchased O-Ring 1736, ordered by customer name.

  5. List the name and number of purchases made at each store, sorted by store name.

  6. List the name and total number of (non-distinct) items purchased at each store, sorted by store name.

  7. List the name and total number of (non-distinct) items purchased at each store, sorted by store name, after or on 2018-11-30.

  8. 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|???
    
0 0
Add a comment Improve this question Transcribed image text
Answer #1

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

  

Add a comment
Know the answer?
Add Answer to:
CREATE TABLE customer (    id INTEGER PRIMARY KEY,    customer_name TEXT,    contact_number TEXT );...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • Consider the following table definitions: create table node (node_id integer primary key, node_color varchar(10)); create table...

    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,...

    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...

  • SQL queries and procedures TABLE: Employees Business Rules: EmployeeID is defined as the primary key. Address...

    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...

    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...

    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...

  • (3.5 Marks) Create the table CAR and create the fields with primary key Field Name Data...

    (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...

  • Based on the CREATE TABLE statements, make an ER model of the database. Give suitable names...

    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...

  • -- Schema definition create table Customer (     cid   smallint not null,     name varchar(20),    ...

    -- 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...

  • SQL problem solving help; First 3 tables are finnished; Need help understanding on how to do...

    SQL problem solving help; First 3 tables are finnished; Need help understanding on how to do steps after. First 3 tables are after the first 2 images for reference if needed. //1// CREATE TABLE kr_customer ( Name VARCHAR(40) NOT NULL PRIMARY KEY, City VARCHAR(20), Status CHAR(1) ); //2// CREATE TABLE kr_salesperson ( Name VARCHAR(40) NOT NULL PRIMARY KEY, Age INT, Salary DECIMAL(8, 2) ); //3// CREATE TABLE kr_order ( Order_Number number(3) NOT NULL PRIMARY KEY, Customer_Name VARCHAR(40), Salesperson_Name VARCHAR(40), Amount...

  • A Pages entity in a relational database for tracking web browsing has the following attributes: PageID (the primary key, an integer number, required), PageURL (text, maximum 255 characters, required),...

    A Pages entity in a relational database for tracking web browsing has the following attributes: PageID (the primary key, an integer number, required), PageURL (text, maximum 255 characters, required), IPAddress (text, maximum 15 characters, required), ViewDateTime (date and time the page was viewed, required). (a) Write an SQL Data Definition Language command to create the table for this entity. (b) Write an SQL Data Definition Language command to add a column for a new attribute to this table: ViewDuration (duration...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
Active Questions
ADVERTISEMENT