Questions 114 example select from dreamhomeBooking where va

Questions 1-14

example: select * from dreamhome.Booking where value is null;

Exercises for Midterm: You don\'t need to submit/email these answers to instructor. We will Review them in class. These tables are in CPS3740 database. Using tables Customers Midterm. Products Midterm and Reviews Midterm, dream home. write SQL statements for the following exercises: Display the address for Customer name \'Judy 1\' Display the total number of Products Find the NULL value in dream home. Booking Create Table Test xxxx based on Reviews_Midterm table under CPS3740_2017S Insert records into Test xxxx Delete records from Test xxxx Display the total number of Customers live in Edison Display the total number of unique products been reviewed. Display product names that can make profit > 99 Display the most expensive product name Display customer names don\'t have review\'s Display customer names, total number of ratings and their averaged review ratings (each customer has only one averaged review rating). The output should have customer name in alphabetic order from a to z. Display the product names and their averaged review ratings made by customer who live in Edison (Each product has only one averaged review rating) The output should be sorted with the highest rating at top. Can we know which Customers bought TV from these three tables?

Solution

1.Display the address for Customer name \'Judy1\':-

Query:-

SELECT ADDRESS FROM Customers_Midterm WHERE CUSTOMER_NAME=\'Judy1\';

2.Display the total number of Products:-

QUERY:-

SELECT COUNT(Products) FROM Products_midterm;

3.Find the NULL value in Dreamhome.Booking:-

QUERY:-

SELECT * FROM Dreamhome.Booking WHERE Column_Name IS NULL;

NOTE:-

* If the retrieval of rows to be done based on the comparison with NULL then IS NULL must be used in SELECT query.
* If \"=NULL\" is used then it will not fetch the rows as the comparison with NULL will yield the only NULL, which means the respective row will not be fetched.

4.Create table Test_xxx based on Reviews_midterm table under CPS3740_2017S:-

QUERY:-

CREATE TABLE Test_xxx
( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size),
...........................................................................................................................................
...........................................................................................................................................

...........................................................................................................................................

column_namen data_type(size) );

5.Insert records into Test_xxx:-

Query:-

INSERT INTO Test_xxx(column1,column2,column3,...) VALUES (value1,value2,value3,...);

6.Delete records from Test_xxx:-

QUERY:-

DELETE FROM Test_xxx WHERE Column_Name = Value;

____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

NOTE:-

#More Information Required