city (SELECT city FROM employee WHERE reports_to = 2); 2. SELECT customer.* FROM customer INNER JOIN employee ON customer.city employee.city WHERE reports_to= 2; Query #1 would be more efficient as it is based on primary and foreign keys. O Query #1 would be more efficient as it is not using indexed columns. Both would be the same as both use the same indices for the join and filter. Query #2 would be more efficient as it is based on primary and foreign keys.

city (SELECT city FROM employee WHERE reports_to = 2); 2. SELECT customer.* FROM customer INNER JOIN employee ON customer.city employee.city WHERE reports_to= 2; Query #1 would be more efficient as it is based on primary and foreign keys. O Query #1 would be more efficient as it is not using indexed columns. Both would be the same as both use the same indices for the join and filter. Query #2 would be more efficient as it is based on primary and foreign keys.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Using the WideWorldDistributors database and the Sales schema create a function that takes InvoiceID as an input parameter and returns all the rows from the table InvoiceLines for the InvoiceID value passed into the function. If the result is a NULL return a zero
Why it's useful to have index that partially sort a query if it doesn't present all of the results already sorted?
In Sql Oracle Apex application, Using GROUPING SETS, write a query to show the following groupings:• department_id, manager_id, job_id• manager_id, job_id• department_id, manager_id
SQL Use appropriate clauses and filters to run the query with minimum time complexity. 2. Try to avoid running the queries on unnecessary rows and columns. 3. Share the queries along with a brief description of the code in a python file, so that the results are reproducible. Q1: Write a query to find the students working in a company having more ratings than their seniors EMPLOYEES RATING TABLE student id student name rating senior id designation 1 george 9 3 student 2 micheal 8 4 student 3 mary 6 null senior 4 steve 9 null senior For the above table GEORGE has more rating than his sENIOR
Implement a query containing a subquery to display the total number of rentals for the customer (unknown before query) who belongs to the city London. Exact display as the following use sakila as Database Thank you 100% C 103:1 Result Grid I & Filter Rows: Q Search customer_id first_name last_name Rental_Count 252 МАTTIE HOFFMAN 22 512 СЕCIL VINES 26
SQL Sales people must be trained to make orders. For a sales person to receive credit for an order, the order_date must fall between a training start and end date for that sales person. 1. Write a query that finds all orders where the salesperson did not have an active training on the order date. The following query seems to almost work, but pulls in order id 4 which is not desired. select * from orders t1 join training t2 on t1.salesperson_id = t2.salesperson_id where (order_date not between start_date and end_date or order_date between start_date and end_date is null) group by id;
WorksOn Database: Employee (EmployeeNumber, EmployeeName, BirthDate, Title, Salary, DepartmentNumber) Project (ProjectNumber, ProjectName, Budget, DepartmentNumber) Department (DepartmentNumber, DepartmentName, ManagerNumber) WorksOn (EmployeeNumber, ProjectNumber, Responsibility, Hours) Questions: 1) Write an SQL query that returns the project number and name for projects with a budget no greater than $1,050,000. 2) Write an SQL query that returns all works on records where hours worked is less than 5 and the responsibility is 'Manager'. 3) Write an SQL query that returns the employees (number and name only) who have a title of 'EE' or 'SA' and make more than $50,000. 4) Write an SQL query that returns the employees (name and salary only) in department 'D1' ordered by decreasing salary. 5) Write an SQL query that returns the departments (all fields) ordered by descending department name.
Q2: Design Database for the following scenario and Write SQL queries. Create an ERD for the following scenario. Suppose there is a grocery store near your house. Following can be considered for ERD: A grocery store may have more than one employee. A grocery store has exactly one manager. The manager has one or more sales men working under him. Grocery store has more than one portion for the products. • Each product has a barcode, name, expired date. Many customers can busy many products, but each product is bought by only one customer. Each customer will get an invoice for his /her purchase. The bill invoice has an id.
Which query would be more efficient?1. SELECT *FROM customerWHERE city IN(SELECT cityFROM employeeWHERE reports_to = 2);2. SELECT customer.*FROM customerINNER JOIN employeeON customer.city = employee.cityWHERE reports_to = 2; Both would be the same as both use the same indices for the join and filter. Query #1 would be more efficient as it is based on primary and foreign keys. Query #2 would be more efficient as it is based on primary and foreign keys. Query #1 would be more efficient as it is not using indexed columns.
select * from cast where cast_id=162652763; Use explain to display the query plan without creating any index. Now create an index to speed up the query. Use explain and timer to show the improvement.
how to filter based on a parameter in a sql function ?' I have the following sql function: CREATE FUNCTION Function.FunctionExample { @name UNIQUEIDENTIFIER = NULL, @date DATETIME = NULL, @seconddate DATETIME = NULL } RETURNS @returnTable TABLE { Code INT, Date DATETIME, SecondDate DATETIME } AS BEGIN INSERT INTO @returnTable { Code, Date, SecondDate } SELECT Date1 AS Date, Date1 AS SecondDate FROM Function.FunctionExample WHERE (@Date IS NULL OR CONVERT(DATE, FunctionExample.Date1) >= @Date) AND (@SecondDate IS NULL OR CONVERT(DATE, FunctionExample.Date1) >= @SecondDate) RETURN END What would I have to do If I wanted to filter based on the @name parameter ?
A cake shop named "Whisk" has an online presence. It has multiple flavors of cakes supplied by various vendors. The manager wants to know the cost of the available quantity. Write a sql query to get the total cost of the available cakes. Code should run on Mysql. Data type along with the sample input and output is mentioned below:- Cake: Column Type Cake_Id int Flavor varchar(100) Cost int Producer varchar(20) Stock int Sample Input :- Cake: Cake_id Flavor Cost Producer Stock 1001 Vanilla 450 Just bake 8 1003 Chocolate 500 Flower Aura 12 1004 Red velvet 600 Bakingo 1 1005 Strawberry 700 Bigbasket 2 1006 Cinnamon 900 Creamy 1 1007 Vanilla 900 Creamy 2 1008 Mango 900 Just bake 2 1009 Strawberry 550 Creamy 2 Sample output - 17200