show contact_id, first and last name, city, state,
Please use the attached .sql files to create the tables in your database, then make the queries as below:
1. Write a query to show contact_id, first and last name, city, state, profession, job title, job salary of the person who currently has a job.
2. Write a query to show contact_id, first and last name, city, state, profession of the person who has interest ‘Tennis’.
3. Write a query to show contact_id, first and last name, salary, title of the person who’s salary is greater than the average of salary.
4. Write a query to show contact_id, first and last name, interest of the person who has any interest but doesn’t have a job.
CREATE TABLE `profession` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `profession` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
/*
-- Query: SELECT * FROM week8_lab_db.profession
LIMIT 0, 1000
-- Date: 2017-03-14 21:33
*/
INSERT INTO `profession` (`id`,`profession`) VALUES (1,'Artist');
INSERT INTO `profession` (`id`,`profession`) VALUES (2,'Baker');
INSERT INTO `profession` (`id`,`profession`) VALUES (3,'Computer Programmer');
INSERT INTO `profession` (`id`,`profession`) VALUES (4,'Student');
INSERT INTO `profession` (`id`,`profession`) VALUES (5,'Teacher');
INSERT INTO `profession` (`id`,`profession`) VALUES (6,'Web Designer');
CREATE TABLE `my_contacts` (
 `contact_id` int(11) NOT NULL AUTO_INCREMENT,
 `last_name` varchar(30) DEFAULT NULL,
 `first_name` varchar(20) DEFAULT NULL,
 `email` varchar(50) DEFAULT NULL,
 `gender` char(1) DEFAULT NULL,
 `birthday` date DEFAULT NULL,
 `location` varchar(50) DEFAULT NULL,
 `status` varchar(20) DEFAULT NULL,
 `interests` varchar(100) DEFAULT NULL,
 `seeking` varchar(100) DEFAULT NULL,
 `prof_id` int(11) DEFAULT NULL,
 `zip_code` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
/*
-- Query: SELECT * FROM week8_lab_db.my_contacts
LIMIT 0, 1000
-- Date: 2017-03-14 21:33
*/
INSERT INTO `my_contacts` (`contact_id`,`last_name`,`first_name`,`email`,`gender`,`birthday`,`location`,`status`,`interests`,`seeking`,`prof_id`,`zip_code`) VALUES (1,'Toth','Anne','Anne_Toth@leapinlimos.com','F','1969-11-18','San Fran, CA',NULL,NULL,NULL,1,'91745');
INSERT INTO `my_contacts` (`contact_id`,`last_name`,`first_name`,`email`,`gender`,`birthday`,`location`,`status`,`interests`,`seeking`,`prof_id`,`zip_code`) VALUES (2,'Manson','Anne','am86@objectville.net','F','1977-08-09','Seattle, WA',NULL,NULL,NULL,2,'48109');
INSERT INTO `my_contacts` (`contact_id`,`last_name`,`first_name`,`email`,`gender`,`birthday`,`location`,`status`,`interests`,`seeking`,`prof_id`,`zip_code`) VALUES (3,'Hardy','Anne','anneh@b0tt0msup.com','F','1963-04-18','San Fran, CA',NULL,NULL,NULL,5,'91745');
INSERT INTO `my_contacts` (`contact_id`,`last_name`,`first_name`,`email`,`gender`,`birthday`,`location`,`status`,`interests`,`seeking`,`prof_id`,`zip_code`) VALUES (4,'Parker','Anne','annep@starbuzzcoffee.com','F','1983-01-10','San Fran, CA',NULL,NULL,NULL,4,'91745');
INSERT INTO `my_contacts` (`contact_id`,`last_name`,`first_name`,`email`,`gender`,`birthday`,`location`,`status`,`interests`,`seeking`,`prof_id`,`zip_code`) VALUES (5,'Blunt','Anne','anneblunt@breakneckpizza.com','F','1959-10-09','San Fran, CA',NULL,NULL,NULL,6,'48109');
INSERT INTO `my_contacts` (`contact_id`,`last_name`,`first_name`,`email`,`gender`,`birthday`,`location`,`status`,`interests`,`seeking`,`prof_id`,`zip_code`) VALUES (6,'Jacobs','Anne','anne99@objectville.net','F','1968-02-05','San Jose, CA',NULL,NULL,NULL,3,'91745');
CREATE TABLE `job_current` (
 `contact_id` int(11) NOT NULL,
 `title` varchar(30) NOT NULL,
 `salary` decimal(10,2) DEFAULT NULL,
 PRIMARY KEY (`contact_id`),
 CONSTRAINT `contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES `my_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*
-- Query: SELECT * FROM week8_lab_db.job_current
LIMIT 0, 1000
-- Date: 2017-03-14 21:33
*/
INSERT INTO `job_current` (`contact_id`,`title`,`salary`) VALUES (1,'Web Designer',70000.00);
INSERT INTO `job_current` (`contact_id`,`title`,`salary`) VALUES (2,'Computer Programmer',100000.00);
INSERT INTO `job_current` (`contact_id`,`title`,`salary`) VALUES (4,'Web Designer',65000.00);
INSERT INTO `job_current` (`contact_id`,`title`,`salary`) VALUES (5,'Teacher',120000.00);
CREATE TABLE `contact_interest` (
id int not null auto_increment PRIMARY KEY,
 `contact_id` int(11) NOT NULL,
 `interest` varchar(50) NOT NULL,
 CONSTRAINT `c_interest_contact_id_fk` FOREIGN KEY (`contact_id`) REFERENCES `my_contacts` (`contact_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*
-- Query: SELECT * FROM week8_lab_db.contact_interest
LIMIT 0, 1000
-- Date: 2017-03-14 23:17
*/
INSERT INTO `contact_interest` (`id`,`contact_id`,`interest`) VALUES (1,1,'Movies');
INSERT INTO `contact_interest` (`id`,`contact_id`,`interest`) VALUES (2,3,'Reading');
INSERT INTO `contact_interest` (`id`,`contact_id`,`interest`) VALUES (3,6,'Tennis');
Answers
Status NEW
Posted 04 Jan 2018 08:01 AM
My Price 10.00
----------- Â ----------- H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly
Not Rated(0)