Show all the sql query codes and the output with it please Create the following table in your database with the following schema:Table: Users| Column Name| Туре+-+-| int| varchar || id| name+-id is the primary key for thistable.name is the name of the user.Table: Rides| Column Name| туре+-| id| user id| distance| int| int| int+-id is the primary key for thistable.user id is the id of the user who traveled the distance "distance".Add the following data to your tables:Input:Users table:+------+| id| name+-| 1| 2| 3| 4| 7| 13| 19| Alice| Bob| Alex| Donald| Lee| Jonathan| Elvis+----Rides table:+------| idI user id| distance |+-----+---| 1| 2| 3| 4| 5| 1| 2| 3| 7| 13| 19| 120| 317| 222| 100| 31250| 120| 400| 230| 8| 19+----- Return the result table ordered by travelled_distance in descending order, if two or more users traveledthe same distance, order them by their name in ascending order.Hint: Use IFNULL(a,b) function in your select clause where a is the value that should be returned andb is the value that should be returned if a is NULL.The result should be:Output:| nametravelled distance+--| Elvis| Lee| Bob| Jonathan | 312| Alex| Alice| Donald| 450| 450| 317| 222| 120+-Explanation:Elvis and Lee traveled 450 miles, Elvis is the top traveler as his name isalphabetically smaller than Lee.Bob, Jonathan, Alex, and Alice have only one ride and we just order them by thetotal distances of the ride.Donald did not have any rides, the distance traveled by him is 0.

We need to provide sql query codes for the given database schema

Create the following table in your database with the following schema: Table: Users I Column Name 1 Туре | id | name | int | varchar | +-- ---------+ id is the primary key for this table. name is the name of the user. Table: Rides | Column Name 1 Туре | id | user_id I distance | int | int | int +---- id is the primary key for this table. user_id is the id of the user who traveled the distance "distance". Add the following data to your tables: Input: Users table: +----- | id | name ---- | 1 | 2 | 3 | 4 | Alice | Bob | Alex | Donald | Lee | JonathanI | Elvis | 13 | 19 +----- Rides table: +------+ | id | user_id | distance | +---- | 1 | 2 | 3 | 4 | 5 | 1 | 2 | 3 | 120 | 317 | 222 | 100 | 312 | 50 | 120 | 400 | 230 | 13 | 19 | 8 | 9 | 19 | 7

Create the following table in your database with the following schema: Table: Users I Column Name 1 Туре | id | name | int | varchar | +-- ---------+ id is the primary key for this table. name is the name of the user. Table: Rides | Column Name 1 Туре | id | user_id I distance | int | int | int +---- id is the primary key for this table. user_id is the id of the user who traveled the distance "distance". Add the following data to your tables: Input: Users table: +----- | id | name ---- | 1 | 2 | 3 | 4 | Alice | Bob | Alex | Donald | Lee | JonathanI | Elvis | 13 | 19 +----- Rides table: +------+ | id | user_id | distance | +---- | 1 | 2 | 3 | 4 | 5 | 1 | 2 | 3 | 120 | 317 | 222 | 100 | 312 | 50 | 120 | 400 | 230 | 13 | 19 | 8 | 9 | 19 | 7

A Guide to SQL

9th Edition

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Philip J. Pratt

Chapter7: Database Administration

Section: Chapter Questions

Problem 4SCG: Write, but do not execute, the commands to grant the following privileges: a. User Oliver must be...

See similar textbooks

Similar questions

Write, but do not execute, the commands to grant the following privileges: a. User Oliver must be able to retrieve data from the CONDO_UNIT table. b. Users Crandall and Perez must be able to add new owners and condo units to thedatabase. c. Users Johnson and Klein must be able to change the condo fee of any unit. d. All users must be able to retrieve the unit number, condo fee, and owner number forevery condo unit. e. User Klein must be able to add and delete service categories. f. User Adams must be able to create an index on the SERVICE_REQUEST table. g. Users Adams and Klein must be able to change the structure of the CONDO_UNITtable. h. User Klein must have all privileges on the LOCATION, OWNER, and CONDO_UNITtables.
3.1 Create a database Cat with the following schema: CREATE TABLE owners ( ownerid integer, name text, age integer, PRIMARY KEY (ownerid) ); CREATE TABLE cats ( catid integer, owner integer, name text, breed text, age integer, PRIMARY KEY (catid), FOREIGN KEY (owner) REFERENCES owners); 3.2. Write a SQL query that returns the number of cats, over the age of 10, of each breed of cat. 3.3. Write a SQL query that returns the ownerid/owner of the one cat owner who owns the most cats.
This question relates to creating and altering tables, as well as implementing constraints and referential integrity. Q.1.1 You are required to create the following tables in a database named SICKLEAVE:EMPLOYEESEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYEMPLOYEE_NAME VARCHAR(30) NOT NULLEMPLOYEE_SURNAME VARCHAR(30) NOT NULLDATE_OF_BIRTH DATE NOT NULLDOCTORSDOCTOR_ID VARCHAR(5) NOT NULL PRIMARY KEYDOCTOR_NAME VARCHAR(30) NOT NULLEMPLOYEE_SICKLEAVEEMPLOYEE_ID VARCHAR(5) NOT NULL PRIMARY KEYFOREIGN KEY REFERENCES EMPLOYEES(EMPLOYEE_ID)START_DATE DATE NOT NULL PRIMARY KEYDOCTOR_ID VARCHAR(5) NOT NULLNUMBER_OF_DAYS SMALLINT NOT NULLQ.1.2 Populate the tables created in Q.1.1 with the following data:EMPLOYEESEMPLOYEE_ ID EMPLOYEE_NAME EMPLOYEE_SURNAMEDATE_OF_BIRTHI0001 Dominique Woolridge 1993-04-19I0002 Nico Baird 1991-11-19I0003 Derek Moore 1992-06-24I0004 Neo Petlele 1993-12-29I0005 Andrew Crouch 1994-01-30DOCTORS DOCTOR_ID DOCTOR_NAMED0001 Thabo NtlaliD0002 Deon CoetzeeD0003 Kwezi…
Using the sample database, create the Relational Algebra expression to answer the following queries. The database schema is as follows: Cars (c_id:number, c_brand:string, color:string, c_condition:string) Dealers (d_id:number, d_name:string, d_location:string) Inventory (c_id:number, d_id:number, costpercar:number, instock:number) Note: Inventory represents cars sold by dealers. How would I write a database queries for the following? 1. Find the names of Dealers who sell some Black Car 2. Find the d_id of Dealers who sell some Black or Silver Car 3. Find the d_ids of Dealers who sell some Black Car or are in Miami 4. Find the d_ids of Dealers who sell some Black Car and some Yellow Car 5. Find the d_ids of Dealers who sell every Black Car 6. Find the d_id of Dealers who sell used Cars that are Black
From the database scheme write the query(all tables are empty) 1) Insert your name and your phone as a new tenant 2) Delete your phone number from the table tenant. 3) Create a new contract lease for you. 4) Delete the contract that you created. 5) List the city name, city population and country name. 6) List the cities that have the letters “re” in any part of the name; 7) List the tenant name contract number, rent price, when the contract rent price is bigger equal 500 and lower equal 1000. 8) List all columns from apartments with the services offered (all columns from service). 9) List the building address, the city name and the country name for the building in Canada. 10) List the name of the user with the city name and country name, ordering by name of user.
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the sids of suppliers who supply only red parts. Find the sids of suppliers who supply a red part and a green part. Find the sids of suppliers who supply a red part or a green part. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies. For every supplier that supplies a green part and a…
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the pnames of parts supplied by Acme Widget Suppliers and no one else. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part). For each part, ﬁnd the sname of the supplier who charges the most for that part. Find the sids of suppliers who supply only red parts. Find the…
Part A Create a database called AWESOME and initialize it with the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the SQL script to create the tables, including implied foreign keys, and populate it with sample data. Sample data should be minimum 4 records each and able to prove the queries in part B are accurate. (ie Query # 4 requires a supplier with a specific name to be included) Part B Write the following queries in SQL: Find the pnames of parts for which there is some supplier. Find the snames of suppliers who supply every part. Find the snames of suppliers who supply every red part. Find the pnames of parts supplied by Acme Widget Suppliers and no one else. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers…
A mobile app for booking doctor appointments is running very slowly when accessing data from a database and the programmers think that poorly optimised SQL statements are causing it. There are three places where they think the code might be slow. 1. When users book a new appointment. 2. When the date of an appointment is modified. 3. When a user searches for doctors by name or specialty. Could any of these be improved by creating a database index? Why/Why not? What column or columns (if any) would be appropriate to add an index to?
SQL Commands for: 1.) TOP-N query. Find the ssn, lname, and salary of the four highest paid employees. 2.) TOP-N query. Find the ssn, lname, and salary of the four lowest paid employees 3.) TOP-N query. Find the lowest two salaries in the company.(Notice that in our database, the two lowest salaries are 25K and 30K.) 4.) TOP-N query. For every employee whose salary is equal to one of the two lowest salaries, Find the ssn, lname, and salary. 5.)RANK query Find the rank of the salary 30000 among all salaries. (HINT: The ranks in our database are 1 for 25000, 4 for 30000, 5 for 38000, and so on.) 6.) RANK query ... compare with the previous query. Find the rank of the salary 31000 among all salaries. 7.) DENSE RANK query Find the dense rank of the salary 30000 among all salaries. Hint: The dense ranks in our database are 1 for 25000, 2 for 30000, 3 for 38000, and so on. 8.) DENSE RANK query ... compare with the previous query. Find the dense rank of the salary 31000 among all salaries.…
Write Mysql code for: The database has three tables for tracking horse-riding lessons: Horse with columns: ID - primary key RegisteredName Breed Height BirthDate Student with columns: ID - primary key FirstName LastName Street City State Zip Phone EmailAddress LessonSchedule with columns: HorseID - partial primary key, foreign key references Horse(ID) StudentID - foreign key references Student(ID) LessonDateTime - partial primary key Write a SELECT statement to create a lesson schedule for Feb 1, 2020 with the lesson date/time, student's first and last names, and the horse's registered name. Order the results in ascending order by lesson date/time, then by the horse's registered name. Make sure unassigned lesson times (student ID is NULL) appear in the results. Hint: Perform a join on the LessonSchedule, Student, and Horse tables, matching the student IDs and horse IDs.