Using mySQL, tables at the end...
This assignment uses the tables from the vets database. The goal of the assignment is to get you to think about joins- inner join and outer joins. If you do a join, where you have the names of two or more tables in the From clause, then you must use the condition join syntax or the column name join.
Take care that you do not accidentally do a Cartesian product. If your result set contains a thousand rows ( or even a hundred rows for this assignment) you have probably done a Cartesian product and you need to correct the query.
Do not assume that these will all require outer joins because that was the topic for the week. Use an inner join if that will solve the task; use an outer join only if it is required.
Use the fewest tables possible for the queries. For example if I ask you to find animals with no exams, you need to use the exam headers table. But you do *not* need the exam details table. Sometimes adding extra tables makes your query less efficient; other times it makes your query incorrect.
The use of meaningful table aliases is required in assignments. The table names are fairly long and when you need to qualify a column, that expression gets longer and it can be harder to read. You define the table aliases within in each query. It can help to have a consistent set of table aliases to use. These are the ones I use; you can use them or different aliases that suggest the table.
clients cl
animals an
exam headers eh
exam details ed
Task 04: We want to find animals for which we have no exam records (use the exam header table). Show the client id, and last name and the an_id, name and type for every animal that does not have any exam records in the exam table. Sort by the client id and animal id.
Task 05: For this task use the same logic as in Task 03 except that your results should not display clients who have no animals.
Task 06: Display the client id and client last name and the animal id, name, and date of birth for all animals owned by a client who lives in New York or Massachusetts but do not display rows for any of the dogs, cats or birds.
Task 07: Display data about services we have in the services table that have a list price of $100 or more and that have not been used on any exam. Display the service id, list price, description and service type.
TABLES:
create table vt_staff (
stf_id numeric(6,0)
, stf_name_last char(25) not null
, stf_name_first char(25) not null
, stf_job_title char(25) not null
);
create table vt_staff_pay (
stf_id numeric(6,0)
, stf_ssn char(9) not null
, stf_salary numeric(8,2) not null
, stf_hire_date date not null
);
create table vt_services(
srv_id numeric(6,0)
, srv_list_price numeric(6,2) not null
, srv_desc char(50) not null
, srv_type char(25) not null
);
create table vt_animal_types(
an_type char(25)
);
create table vt_clients(
cl_id numeric(6,0)
, cl_name_last char(25) not null
, cl_name_first char(25) null
, cl_address char(25) not null
, cl_city char(25) not null
, cl_state char(2) not null
, cl_postal_code char(12) not null
, cl_phone char(12) null
);
create table vt_animals(
an_id numeric(6,0)
, an_type char(25) not null
, an_name char(25) null
, an_dob date not null
, cl_id numeric(6,0) not null
);
create table vt_exam_headers(
ex_id numeric(6,0)
, an_id numeric(6,0) not null
, stf_id numeric(6,0) not null
, ex_date date not null
);
create table vt_exam_details(
ex_id numeric(6,0) not null
, line_item numeric(6,0) not null
, srv_id numeric(6,0) not null
, ex_fee numeric(6,2) not null
, ex_desc char(50) not null
);
Task 04) select
cl.cl_id,cl.cl_name_last,an.an_id,an.an_type,an.an_name
from clients cl,animals an
where cl.cl_id=an.cl_id
and an.an_id not in(select an_id from vt_exam_headers)
order by cl.cl_id,an.an_id;
Task 05)
select
cl.cl_id,cl.cl_name_last,an.an_id,an.an_type,an.an_name
from clients cl,animals an
where cl.cl_id=an.cl_id
order by cl.cl_id,an.an_id;
Task 06) select cl.cl_id,cl.cl_name_last,
an.an_id,an.an_name,an.an_dob
from clients cl, animals an
where cl.cl_id=an.an_id
and cl.cl_state in('New York','Massachusetts')
and an.an_type not in('dogs','cats','birds');
Task 07) select
s.srv_id,s.srv_list_price,s.srv_desc,srv.srv_type
from vt_services s
where s.srv_id not in(select srv_id from vt_exam_details)
and s.srv_list_price>=100;
Using mySQL, tables at the end... This assignment uses the tables from the vets database. The goa...
Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys. Insert data into each table. Show select statements and display the output of each table. Note:Student’s name must be inserted into table as part of the data! Perform the SQL below: Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and...
More on JOINS Log in to your ApEX SQL account. Two Table Join Create a SQL query that gets the order ID, order_mode, customer ID and product ID from the ORDERS and ORDER_ITEMS tables. You will have to join the tables on some common column. Two Table Join With Subquery Modify the previous query and add a subquery that looks for the existence of a customer ID in the CUSTOMERS table. This is a correlated sub-query so you will have...
#1) Display Last Name, First Name, DonorId, Fundname, TotalPledged from the donor’s database (you may need to use donor and pledge tables to get the information) for Donor ID 59034. (Your results window should have 3 rows) #2) Display all records from the PurchaseOrderHeader from the AdventureWorks Database that were sold by Employee 261 (Your results window should have 401 rows) #3) Display salesorderid, orderdate, totaldue, and territory name from salesorderheader and salesterritory for all totaldue that are greater than...
This is database system concept. 1.Find the ids of instructors who are also students using a set operation. Assume that a person is identified by her or his id. So, if the same id appears in both instructor and student, then that person is both an instructor and a student. Remember: set operation means union, intersect or set difference. 2.Find the ids of instructors who are also students using the set membership operator. 3.Find the ids of instructors who are...
Form A Mid-Term Exam In ajoin, when using the ON keyword, column names need to be qualified only A in inner joins B. in outer joins c when the code is confusing D. when the same column names exist in both tables 7. Referring to the following code example and the data model the total number of rows returned by this query must equal SELECT vendor name, Invoice number FROM invoices LEFT JOIN vendors ON Invoices. vendor id = vendors.vendor...
Topic: Inventory Manangement Part A. Database Creation Your database should have a minimum of 4 tables and include both one-to-many and many-to-many relationships. Be sure to include some numeric and/or date fields. Define all appropriate constraints, using the proper naming conventions (see Structure Notes below). Populate your database with at least 30 records in the main table(s), and whatever is needed in related tables. Submit the following: • a short description of the purpose of the database and what the...
SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT NULL, PRIMARY KEY (vid) ); CREATE TABLE category ( catid CHAR(2) NOT NULL, catname VARCHAR(25) NOT NULL, PRIMARY KEY (catid) ); CREATE TABLE product ( pid CHAR(3) NOT NULL, pname VARCHAR(25) NOT NULL, price NUMERIC (7,2) NOT NULL, vid CHAR(2) NOT NULL, categoryid CHAR(2) NOT NULL, PRIMARY KEY (pid)); CREATE TABLE region ( rid CHAR NOT NULL, rname VARCHAR(25) NOT NULL, PRIMARY KEY (rid)...
these are all tables please see the tables and questions are mentioned below please see that all and I need answers asap please write proper answer it's an easy task and don't take much time please do it fast thanks in advance EMPLOYEE Remark Column Name EmployeeNumberINT Primary Key Yes No CHAR (25 CHAR (35 CHAR 25 NUMERIC INT CHAR (12 CHAR Name in the DEPARTMENT table Position No Number in the EMPLOYEE table Su OfficePhone EmailAddress No No No...
Using the MySQL Workbench create a new database using your design specifications Add at least 10 records to your tables. Note: Certain tables may not require 10 records and that is ok as long as your main tables have 10 or more Create MySQL statements that will retrieve all records or rows from the tables in your database Create 10 MySQL statements that will retrieve specified records or rows from one table in your database Create 10 MySQL statements that...
Tables: Create table Item( ItemId char(5) constraint itmid_unique primary key, Decription varchar2(30), Unitcost number(7,2)); Create table Customer( custID char(5) constraint cid.unique primary key, custName varchar2(20), address varchar2(50)); Create table Orderdata( orderID char(5) constraint oid_uniq primary key, orderdate date, shipdate date, ItemId char(5) references Item.ItemId, No_of_items number(4), Unitcost number(7,2), Order_total number(7,2), custID char(5) references customer.custID); Insert Into Item values(‘A123’,’Pencil’,2.5); Insert Into Item values(‘B123’,’Pen’,15); Insert Into...