Using the below tables in the stated database create a query that JOINS the aforementioned tables and produces the expected result set. Note: Your query must be well formatted and runnable for credit. i.e.. It can be pasted into SSMS and executed producing the expected results. Paste your well formatted query below for credit. • Database • Northwind Tables • [dbo].[Employees] • [dbo].[EmployeeTerritories] • [dbo].[Territories] • Result Set • FullName · Note: this will be a concatenation of LastName, FirstName • Address • PostalCode TerritoryDescription

Using the below tables in the stated database create a query that JOINS the aforementioned tables and produces the expected result set. Note: Your query must be well formatted and runnable for credit. i.e.. It can be pasted into SSMS and executed producing the expected results. Paste your well formatted query below for credit. • Database • Northwind Tables • [dbo].[Employees] • [dbo].[EmployeeTerritories] • [dbo].[Territories] • Result Set • FullName · Note: this will be a concatenation of LastName, FirstName • Address • PostalCode TerritoryDescription

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter13: Views

Section: Chapter Questions

Problem 2MC

See similar textbooks

Similar questions

Write the following queries, based on the database schema Product(maker, model, type)PC(model, speed, ram, hd, price)Laptop(model, speed, ram, hd, screen, price)Printer(model, color, type, price) of Exercise 2.4.1. You should use at least one subquery in each of your answersand write each query in two significantly different ways (e.g., using differentsets of the operators EXISTS, IN, ALL, and ANY). a) Find the makers of PC's with a speed of at least 3.0.b) Find the printers with the highest price.! c) Find the laptops whose speed is slower than that of any PC.! d) Find the model number of the item (PC, laptop, or printer) with thehighest price.! e) Find the maker of the color printer with the lowest price.!! f) Find the maker(s) of the PC(s) with the fastest processor among all thosePC's that have the smallest amount of RAM.
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…
To make the TAL Distributors database run more efficiently, the DBA denormalized part of the data, resulting in the following structure for one of the tables: A third normal form violation has occurred in the Customer table, which contains the following fields: CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, and RepName. Which field(s) are to blame? What would the typical shape of the denormalized table be in this case?
Use Microsoft SQL Server Management Studio for this practical i. Create a database called ‘ORG’ ii. create and populate the following tables: First table CREATE TABLE Worker (WORKER_ID INT NOT NULL PRIMARY KEY, FIRST_NAME CHAR(25), LAST_NAME CHAR(25), SALARY INT, JOINING_DATE DATETIME, DEPARTMENT CHAR(25) ); 2INSERT INTO Worker (WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES (001, 'Monika', 'Arora', 100000, '2014-02-20 09:00:00', 'HR'),(002, 'Niharika', 'Verma', 80000, '2014-06-11 09:00:00', 'Admin'),(003, 'Vishal', 'Singhal', 300000, '2014-02-20 09:00:00', 'HR'),(004, 'Amitabh', 'Singh', 500000, '2014-02-20 09:00:00', 'Admin'),(005, 'Vivek', 'Bhati', 500000, '2014-06-11 09:00:00', 'Admin')); Second table CREATE TABLE Bonus (WORKER_REF_ID INT,BONUS_AMOUNT INT,BONUS_DATE DATETIME,FOREIGN KEY (WORKER_REF_ID) REFERENCES Worker(WORKER_ID)); INSERT INTO Bonus (WORKER_REF_ID, BONUS_AMOUNT, BONUS_DATE) VALUES(001, 5000, '2016-02-20'),(002, 3000,…
Let's assume that the following indices are now set on the schema from the previous task: - Level 2 B+ index on the attribute Vehicle.year - Hash index on the attribute Rental.date Our DBMS has the following algorithms to perform the join: - Block nested loops join - Index nested loops join Translate the following SQL query into relational algebra and find the plan that reads the fewest pages or blocks: SELECT Vehicle.brand, Vehicle.model FROM Vehicle JOIN Rental ON (Vehicle.vid = Rental.vid) WHERE Vehicle.year = 2015 AND Rental.date > ’31.12.2022’
Using the TAL Distributors Database, determine the functional dependencies that exist in the following table: ITEM (ITEM_NUM, DESCRIPTION, ON_HAND, CATEGORY, STOREHOUSE, PRICE, (ORDER_NUM, ORDER_DATE, CUSTOMER_NUM, CUSTOMER_NAME, NUM_ORDERED, QUOTED_PRICE)) After determining the functional dependencies, convert the table to an equivalent collection of tables in third normal form. List table definitions using the following notation: TABLE_NAME (PRIMARY_KEY, ATTRIBUTE1, ATTRIBUTE2...ATTRIBUTEN) Use LucidChart to model your resulting tables and the relationships as an ERD. Include your table definitions as a text box on your diagram. Export your solution as a pdf and submit it here.
In database management, what is the function of the cross join operators while managing data using SQL join operators? Choose an answer A Retrieve data from tables B Returning rows having matched values C Identifying cartesian product of two tables D Adding values of two rows
The DBA denormalized certain data in the TAL Distributors database for the sake of speed, and one of the resultant tables looks like this: Customer table is not in third normal form due to the following field(s): CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum, RepName. Under this situation, what does the normal form of the denormalized table look like?
You are given the following database schema:Sailors(sid, sname, rating, age)Boats(bid, bname, color)Reserves(sid, bid, day)Consider the join on Sailors ▷◁ Reserves and assume that you have the following statistics andlimitations: * You have available 9 pages in main memory.* Sailors: 100000 tuples, 1000 pages; Boats: 2000 tuples, 10 pages; Reserves: 110000 tuples,550 pages 1. Calculate the I/O cost of this join using the Block Nested Loop algorithm?
Please answer the following in SQL: Find all pairs of customers who have purchased the exact same combination of cookie flavors. For example, customers with ID 1 and 10 have each purchased at least one Marzipan cookie and neither customer has purchased any other flavor of cookie. Report each pair of customers just once, sort by the numerically lower customer ID. The MySQL-specific GROUP_CONCAT and JSON_ARRAYAGG functions are not permitted. customers dataset CId: unique identifier of the customerLastName: last name of the customerFirstName: first name of the customer goods dataset GId: unique identifier of the baked goodFlavor: flavor/type of the good (e.g., "chocolate", "lemon") Food: category of the good (e.g., "cake", "tart")Price: price (in dollars) items dataset Receipt : receipt number Ordinal : position of the purchased item on the receipts. (i.e., first purchased item, second purchased item, etc...)Item : identifier of the item purchased (see…
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.
Use the information below to answer the question. What types of additional information and constraints would you like to represent in the schema? Think of several users of your database, and design a view for each. CREATE TABLE USER1 (SSN INT NOT NULL, FNAME VARCHAR2(20), M VARCHAR(1), LNAME VARCHAR2(20), ADDRESS VARCHAR2(100), PHONE INT, AGE INT, SEX VARCHAR(1), JOBTYPE VARCHAR2(30), ASTYPE VARCHAR2(30), ETYPE VARCHAR2(30), SHIFT VARCHAR2(20), POSITION VARCHAR2(30), AP_NAME VARCHAR2(100), PRIMARY KEY(SSN), FOREIGN KEY(AP_NAME) REFERENCES AIRPORT(AP_NAME) ON DELETE CASCADE); INSERT INTO USER1(SSN, FNAME, M, LNAME, ADDRESS, PHONE, AGE, SEX, JOBTYPE, ASTYPE, ETYPE, SHIFT, POSITION, AP_NAME) VALUES(123456789,'LINDA','M','GOODMAN','731 Fondren, Houston,…

SEE MORE QUESTIONS

Recommended textbooks for you

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:

9781305251038

Author:

Joan Casteel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS