Describe what each line does in the following SQL query The lyrics database is provided under...

Describe what each line does in the following SQL query

The lyrics database is provided under question 3 for context

1. select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300

2. select concat_ws(' ', firstname, lastname) as "Member Name"

from members;

3. select m.lastname, m.firstname, s.lastname

from members m inner join salespeople s using (salesID)

order by m.lastname asc;

The lyrics database is provided below

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
        ArtistID int, 
        ArtistName varchar (50) NOT NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        EntryDate date NULL ,
        LeadSource varchar (10) NULL 
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
        Genre varchar (15)  
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
        MemberID int ,
        FirstName varchar (25) NULL ,
        LastName varchar (25) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        HomePhone varchar (16) NULL ,
        WorkPhone varchar (16) NULL ,
        EMail varchar (40) NULL ,
        Gender char (1) NULL ,
        Birthday date NULL ,
        SalesID smallint NULL 
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);


CREATE TABLE SalesPeople (
        SalesID smallint ,
        FirstName varchar (20) NOT NULL ,
        LastName varchar (20) NOT NULL ,
        Initials varchar (3) NULL ,
        Base decimal(5,2) NULL,
        Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);      


CREATE TABLE Studios (
        StudioID int,
        StudioName varchar (40) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        Contact varchar (50) NULL ,
        EMail varchar (40) NULL ,
        Phone varchar (16) NULL ,
        SalesID smallint NULL 
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);


CREATE TABLE Titles (
        TitleID int ,
        ArtistID int NULL ,
        Title varchar (50) NULL ,
        StudioID int NULL ,
        UPC varchar (13) NULL ,
        Genre varchar (15) NULL 
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');


CREATE TABLE Tracks (
        TitleID int NOT NULL ,
        TrackNum smallint NOT NULL ,
        TrackTitle varchar (50) NULL ,
        LengthSeconds smallint NULL ,
        MP3 smallint NULL ,
        RealAud smallint NULL 
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);


CREATE TABLE XrefArtistsMembers (
        MemberID int NOT NULL ,
        ArtistID int NOT NULL ,
        RespParty smallint NOT NULL 
       );

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

Solutions

Expert Solution

select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300

this query returns studioID, studioname and base from salespeople and table studios. Here inner join is used hence both table entries should be matched. It will returns row on condtion salespeople.salesID=studios.salesid and base should be less 300.

----------------------------------------------------------------------------------------------------------------------------------------------------------

select concat_ws(' ', firstname, lastname) as "Member Name"

from members;

Here concat_ws() function adds two or more expressions together with a separator. here separator is ' '. Column firstname and lastname concate separated with ' '.

-------------------------------------------------------------------------------------------------------------------------------------------------------

select m.lastname, m.firstname, s.lastname

from members m inner join salespeople s using (salesID)

order by m.lastname asc;

it will select lastname, firstname from members and lastname from salespeople on salesID where salesID is same in both tables

USING is useful when both the tables share a column of the exact same name on which they join.

venereology answered 2 years ago

Next > < Previous

Related Solutions

Describe in words what the function of each line is in the following SQL query The...

Describe in words what the function of each line is in the following SQL query The lyrics database is provided under question 3 for context 1. select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300 2. SELECT artistName FROM Artists WHERE artistID IN (SELECT artistID FROM Titles) 3. select m.lastname, m.firstname, s.lastname from members m inner join salespeople s using (salesID) order by m.lastname asc; The lyrics database...

SQL query exercises: Consider the following database schema: Product(maker, model, type) &

SQL query exercises: Consider the following database schema: Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) Consider the Drivers-Cars-Reserves DB for a small rental car company: Drivers(id, name, rating, age) Cars(vin, maker, model, year, color) Reserves(did, vin, date) Give SQL statement each of the following operations: Find the colors of cars reserved by Smith. Find all id’s of drivers who have a...

Create the following SQL queries using the lyrics database below 1. List the first name, last...

Create the following SQL queries using the lyrics database below 1. List the first name, last name, and region of members who do not have an email. 2. List the first name, last name, and region of members who do not have an email and they either have a homephone ending with a 2 or a 3. 3. List the number of track titles that begin with the letter 's' and the average length of these tracks in seconds 4....

the mysql lyrics database is provided below 1.)What is a primary key used for? Can you...

the mysql lyrics database is provided below 1.)What is a primary key used for? Can you give me an example of a primary key in the Lyrics database? 2.)What is the purpose of doing a join between two tables. Ex. Why would I ever want to join the Tracks and Titles tables together? 3.)Why is isolation important in database design? DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers; DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties; DROP TABLES IF EXISTS Products,Customers,Orders,Order_details; DROP TABLES...

1. For each of the following, write a single SELECT query against the TSQLV4 database that...

1. For each of the following, write a single SELECT query against the TSQLV4 database that returns the result set described. Each of these queries involves two tables and can be written using a join operation. a. One row for each order shipped to France or Germany, showing the order ID, the last name of the employee for the order, and the customer ID for the order. b. One row for each employee who handled orders to Belgium, showing the...

using the lyrics database schema in mysql provided below. 1.)List the artist name of the artists...

using the lyrics database schema in mysql provided below. 1.)List the artist name of the artists who do not have a webaddress and their leadsource is “Directmail”? 2.)List the names of members in the artist called 'Today'. 3.)Report the total runtime in minutes FOR EACH album in the Titles table. 4.)List the firstname, lastname of members who are represented by the salesperson “Lisa Williams” 5.)List EACH salesperson’s firstname along with the number of Members that EACH SalesPerson represents. below is...

SQL DATABASE Task 2 [10.5 marks] using the AdditionCollege database For task 2, we have provided...

SQL DATABASE Task 2 [10.5 marks] using the AdditionCollege database For task 2, we have provided you with the creation script for the AdditionCollege database. Run this script in MySQL Workbench to create the database. You should execute your query solutions to extract the necessary information. The script is based on the following schematic: Unit (Unit_code, Staff_id, [UnitName]) Staff (Staff_id, StaffName, Position, Gender) Taught_by (Unit_code, Staff_id, weekday) Student (Student_id, Student_name, Address, Gender) TuteGroup (TuteGroup_code, Unit_code, DayHrCode, Room_Nr) TuteGroup_List (TuteGroup_code, Student_id)...

Please use the books database pasted under question 4 to design the following SQL queries. Use...

Please use the books database pasted under question 4 to design the following SQL queries. Use any method such as subqueries, equi-join/inner-join, outer join, EXISTS 1. Find the name(s) of the publisher(s) who have published the computer book. 2. Find the name(s) of the author(s) that have authored more than one books. 3. Find the name(s) of the publisher(s) who published the least expensive book. 4. Find the name(s) of the author(s) who wrote the book with the greatest number...

Please use the books database pasted under question 4 to design the following SQL queries. Use...

Please use the books database pasted under question 4 to design the following SQL queries. Use any method such as subqueries, equi-join/inner-join, outer join, EXISTS 1. List the title_name and book type of the books that are published earlier than the earliest biography book 2. List the title_name and book type of the books published by 'Abatis Publishers' 3. Find the name(s) of the publisher(s) that have not published any book 4. Find the name(s) of the publisher(s) who have...

What is a Data Dictionary? What is a Database Engine? What is a Query Processor/Analyzer? What...

What is a Data Dictionary? What is a Database Engine? What is a Query Processor/Analyzer? What is a Forms Generator? What is a Reports writer? What is a DBMS? What is the difference between DB and DBMS?

Subjects