Question 6 Consider the following table schema: CREATE TABLE members( member_id INT, first_name VARCHAR2 (100) NOT NULL, last_name VARCHAR2 (100) NOT NULL, gender CHAR (1) NOT NULL, DOBYear Number NOT NULL, email VARCHAR2 (255) NOT NULL, PRIMARY KEY (member_id) ) ; For the following query on the above table: select max (DOBYear) from members m where gender='F'; Identify for which column you will create indexes to make the above query more efficient? What type of indexes you will create? Give SQL for creating these indexes. Give reason for your answers where ever it is appropriate.

Question 6 Consider the following table schema: CREATE TABLE members( member_id INT, first_name VARCHAR2 (100) NOT NULL, last_name VARCHAR2 (100) NOT NULL, gender CHAR (1) NOT NULL, DOBYear Number NOT NULL, email VARCHAR2 (255) NOT NULL, PRIMARY KEY (member_id) ) ; For the following query on the above table: select max (DOBYear) from members m where gender='F'; Identify for which column you will create indexes to make the above query more efficient? What type of indexes you will create? Give SQL for creating these indexes. Give reason for your answers where ever it is appropriate.

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

q22
Personal Insurance, Inc. is a national company that insures homeowners and renters. It also offers umbrella policies that provide additional coverage. As a regional manager, you need to be able to query the database to help make decisions and to answer questions from other employees. Create a query based on the Customers table in Query Design View with the following options: Add the CustomerID, FirstName, LastName, and DateOfBirth fields to the design grid in that order. Sort the records in ascending order by LastName. Save the query using CustomerLastNameSorted as the name.Open the query in Datasheet View, then close it, saving if necessary. Open the HomeownersPremium query in Design View and make the following changes to the query: Delete the CustomerID column from the design grid. Add criteria to select only those records where the Premium field value is greater than 2,000. Save the changes to the HomeownersPremiumOpen the query in Datasheet View, then close it, saving if…
Task 2: The Driver Relationship team wants to ensure that the all driving license IDs in the active drivers table have the length of 7. Alter the ACTIVE_DRIVERS table created in Chapter 8, Activity 1 to check the length of the DRIVER_DRIVING_LICENSE_ID. Add a CHECK to the ACTIVE_DRIVERS table to enforce a driver license length of 7. Test Query: SELECT DISTINCT(constraint_name) FROM information_schema.check_constraints WHERE constraint_schema = 'InstantRide' AND constraint_name = 'ACTIVE_DRIVERS_chk_1' Expected Results CONSTRAINT_NAME ACTIVE_DRIVERS_chk_1
Please give a detailed solution for the problem below.
Using the Henry Books database, add the new title from Stephen King to the wrote table: Book code = 3578 Author number = 6 Edition = 1 You will insert 2 snips for this question. What construct did you use to add a row to the wrote table. Insert the snip of the construct used: Insert the snip of the wrote table. Be sure the new row is displayed:
Write an SQL query that will select OrderID and OrderDate from theOrders table and OrderDetailID from the OrderDetails table. The queryshould join on the Orders table using the OrderID attribute.
Question 20 The query in the figure below: Field: LastName Table: MEMBERS Sort: Show: Criteria: Like "N*" or A) All Names hat start th the len B) AI Names ending with the letter N AINamesthar cor the letrerN as withe
Create a View to show the city name of any location that has a department. Only show the city name once in the results. You must use a non-correlated subquery.Call this view: Department_LocationsInclude a second query to display from the View after it is created. Refer to the attached for the table.
q17
PLZ help with the following using ORACLE command sql Use subqueries CREATE TABLE MEMBERSHIP ( MEM_NUM CHAR(3) CONSTRAINT MEMBER_MEM_NUM_PK PRIMARY KEY, MEM_FNAME VARCHAR(30) NOT NULL, MEM_LNAME VARCHAR(30) NOT NULL, MEM_STREET VARCHAR(15), MEM_CITY VARCHAR(10), MEM_STATE CHAR(2), MEM_ZIP CHAR(5), MEM_BALANCE NUMBER (2)); CREATE TABLE RENTAL ( RENT_NUM CHAR(4) CONSTRAINT RENTAL_RENT_NUM_PK PRIMARY KEY, RENT_DATE DATE, MEM_NUM CHAR(3), CONSTRAINT RENTAL_MEM_NUM_FK FOREIGN KEY (MEM_NUM) REFERENCES MEMBERSHIP); CREATE TABLE PRICE (PRICE_CODE CHAR(1) CONSTRAINT PRICE_PRICE_CODE_PK PRIMARY KEY, PRICE_DESC VARCHAR(20), PRICE_RENTFEE NUMBER (3,1), PRICE_DAILYATFEE NUMBER(3,1)); CREATE TABLE MOVIE (MOVIE_NUM CHAR(4) CONSTRAINT MOVIE_MOVIE_NUM_PK PRIMARY KEY, MOVIE_NAME VARCHAR(30) NOT NULL, MOVIE_YEAR CHAR(4), MOVIE_COST NUMBER(5,2), MOVIE_GENRE VARCHAR(15), PRICE_CODE CHAR(1), CONSTRAINT MOVIE_PRICE_CODE_FK FOREIGN KEY (PRICE_CODE) REFERENCES PRICE); CREATE TABLE VIDEO…
i need to create a query that joins these 3 tables, without the duplicate column which would be the shop_id. i cant get them to join without that column duplicating. help would be appreciated thanks! CREATE TABLE COFFEE_SHOP ( shop_id int, shop_name VARCHAR(50), city VARCHAR(50),state CHAR(2), PRIMARY KEY (shop_id)); CREATE TABLE EMPLOYEE ( employee_id int, first_name VARCHAR(30), last_name VARCHAR(30),hire_date Date,job_title VARCHAR(30),shop_id int, PRIMARY KEY (employee_id),FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id)); CREATE TABLE COFFEE ( coffee_id int, shop_id int, supplier_id int,coffee_name VARCHAR(30),price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id),FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id),FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id));
ers6ydrhg