**Example Task for an Educational Website:****Task 15: Creating a Contact List with Marital Status**Your task is to list the first name of all contacts along with their marital status, indicating whether they have a spouse (yes/no). Sort the list by the first name. An example of how your result might appear is displayed below:```+-----------+---------+| firstname | Married?|+-----------+---------+| Biff | no || George | yes || Jennifer | no || Lorraine | yes || Marty | no |+-----------+---------+```**Explanation of the Table:**- The table is organized with two columns: - The first column labeled "firstname" holds individuals' first names. - The second column labeled "Married?" indicates the individual's marital status with either "yes" (indicating they have a spouse) or "no" (indicating they do not).- The contacts are ordered alphabetically by their first names in the list. For the remaining questions, use the following table of people:**people** (id *(pk)*, firstname, lastname, spouse_id *(fk)*)The table was created as:```sqlCREATE TABLE people ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30), lastname VARCHAR(30), spouse_id INT, CONSTRAINT spouse_fk FOREIGN KEY (spouse_id) REFERENCES people (id));```This is some sample data:**Participation Activity 9**```+----+-----------+-----------+-----------+| id | firstname | lastname | spouse_id |+----+-----------+-----------+-----------+| 1 | Marty | McFly | NULL || 2 | Jennifer | Parker | NULL || 3 | Lorraine | McFly | 5 || 4 | Biff | Tannen | NULL || 5 | George | McFly | 3 |+----+-----------+-----------+-----------+```In this table:- `id`: An integer that serves as the primary key for the table.- `firstname`: A string representing the person's first name, with a maximum length of 30 characters.- `lastname`: A string representing the person's last name, with a maximum length of 30 characters.- `spouse_id`: An integer that is a foreign key referencing the `id` in the same table, indicating the spouse's ID if applicable, otherwise `NULL`. The sample data provides five entries with various relationships, including NULL values for those who are not married (i.e., not assigned a spouse ID).

Structured Query Language : SQL ( Structured Query Language) is an area explicit language utilized…

15. List the first name for all contacts and whether they have a spouse or not (yes/no). Order by the first name. For example, your result set might look like this: I fÅKstname | Married? | Biff | George | Jennifer | Lorraine | Marty | no | yes | no | yes | no

15. List the first name for all contacts and whether they have a spouse or not (yes/no). Order by the first name. For example, your result set might look like this: I fÅKstname | Married? | Biff | George | Jennifer | Lorraine | Marty | no | yes | no | yes | no

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

Task 1: The InstantRide received some traffic violation tickets from the government. The Legal team of InstantRide requires the travel information of the respective drivers along with corresponding Driving License IDs to proceed further. In addition, the team wants to include the drivers without travel information in the system yet for the completion of driver list. Therefore, you need to return DRIVER_FIRST_NAME, DRIVER_LAST_NAME, DRIVER_DRIVING_LICENSE_ID, TRAVEL_START_TIME, TRAVEL_END_TIME information from the DRIVERS and TRAVELS data connected by LEFT JOIN. Task: Query all drivers with and without travel data. (SQL Database Test)
SELECT P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE,MAX(AVGPRICE) FROM LGPRODUCT P INNER JOIN LGBRAND B ON P.BRAND_ID = B.BRAND_ID (SELECT P.BRAND_ID, AVG (P.PROD_PRICE) AS AVGPRICE FROM LGPRODUCT P GROUP BY P.BRAND_ID)AS AVG_PRICE GROUP BY P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT P.BRAND_ID, AVG (P.PROD_PRICE) AS AVGPRICE FROM LGPRODUCT P GROUP BY P.' at line 3
Corporation: id 15 17 20 instance: Company: cid id 1 2 3 4 5 15 17 15 20 15 Assumptions: id in Company references id in Corporation. • cid in Department references cid in Company. Department: did cid 1 2 3 4 5 6 7 8 9 10 11 12 5 1 2 2 3 5 1 3 3 1 2 4 Each option starts from the original tables (given above) NOT from the tables modified by the previous option You don't need to copy the entire table. Just explain the action of each operation. (a) Suppose that all the foreign keys are declared using on delete restrict. What is the effect of the instruction delete from Company where cid=2? (b) Suppose that all the foreign keys are declared using on delete cascade. What is the effect of the instruction delete from Corporation where id-15?
Below are some rows of the PROVIDERS table for a factory: PCODE 10192 98411 0489035 NAME Hans, Inc. Tools and More Motors Inc. PHONE 231 - 3092 231-1029 993 - 1821 All providers from the factory zone of the city start with 231. You want to get the providers that are in the factory zone of the city. Which of the following is more appropriate? O a. O b. SELECT * FROM PROVIDERS WHERE PHONE LIKE '% 231' O C. O d. SELECT * FROM PROVIDERS WHERE PHONE LIKE $231%' SELECT * FROM PROVIDERS WHERE PHONE LIKE 231 SELECT * FROM PROVIDERS WHERE PHONE LIKE '%231%'
Data mining I NEED THE CALCULATION NOT THE THEORY Correct and detailed answer will be Upvoted else downvoted directly. Thank you!
Apply Normalization A table with the students and their grades in different topics. UnitID | StudentID | Date TutorID | Topic Room | Grade Book 5 U2 U1 U5 U4 St1 St1 St4 St2 St2 23.02.03 Tut1 18.11.02 Tut3 23.02.03 Tut1 05.05.03 Tut3 04.07.03 Tut5 GMT 629 Gln 631 5.1 4.7 GMT 629 4.3 PhF 632 AVQ 621 4.9 5.0 Deumlich Zehnder Deumlich TutEmail tut1@fhbb.ch tut3@fhbb.ch tut1@fhbb.ch Dümmlers tut3@fhbb.ch Swiss Topo tut5@fhbb.ch
Query 3: Write a parameter query to display the names of all prospects each member tried to recruit based on the member’s first name and the member’s last name you input. List the member’s First Name, member’s Last Name, prospect’s First Name, and prospect’s Last Name (in this order in the query grid). Display the member’s First Name Heading as Member First Name, member’s Last Name Heading as Member Last Name, prospect’s first name heading as Prospect First Name, and prospect’s last name heading as Prospect Last Name. Sort the list by Member Last Name, Member First Name, Prospect Last Name, and Prospect First Name, all ascending order. (WE ARE USING ACCESS SO I AM JUST TRYING TO UNDERSTAND WHAT TO PUT AND PLUG IN ETC. USE MY PICTURES AS REFERRENCE!)
Find the number of employees hired in January of all years
Composite datatypes can only have multi row &multi column values in them Answers: Yes No
Look up student MAJOR and ROOM information based on the student NAME. Enter formula into B2. 1 NAME MAJOR ROOM 2 58 3 4 NAME MAJOR ROOM 5 $1 CIS DBH 6 S2 7 3 A/F DBH A/F DBH 8 $4 мом SCI 9 S5 мом DBH 10 56 CIS SCI 11 S7 CIS DBH 12 S8 A/F 13 S9 мом 14 S10 CIS Size Color Price Item T. 15 Size L T M 16 Color R. 17 Price 28 H. 29 30 XL R. 18
• Write a simple stored function that Takes a gender as an input ● Find the staff whose gender matches the input Return the results Write a simple stored procedure that Takes a gender as an input Find the staff whose gender matches the input • Print the results
SQL Query Relational Algebra select avg(customer.age) as "avg_age" from AVG(avg_age) (customer) customer; 1. Select customer phone, customer.address, customer.fullname from customer where exists (select * from billing where billing.cid = customer. Customer id); 2. Select * from customer where customer.address in ('Ibri'); 3. Select billing.bill id from billing Group by bill id Having count (case when billing.b status = 'REJWCTED' then 1 end) >1; select * from customer order by full name, age;