ZuoTi.Pro
Question

Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it...

Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it to create tables and populate with the supplied data.

If there are any errors in the script fix them. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module.

Then answer all questions. Write the answer below each question then take screen shot of the result.

This script builds the City Jail database.

The following list reflects common data requests from city managers. Write the SQL statements to

satisfy the requests. If the query can be accomplished by using different operators, supply alternative

solutions so that the performance-tuning group can test them and identify the more efficient

statements.

Test the statements and show execution results.

1. List all criminal aliases beginning with the letter B.

2. List all crimes that occurred (were charged) during the month October 2008. List the crime ID, criminal

ID, date charged, and classification.

3. List all crimes with a status of CA (can appeal) or IA (in appeal). List the crime ID, criminal ID,

charged, and status.

4. List all crimes classified as a felony. List the crime ID, criminal ID, date charged, and classification.

5. List all crimes with a hearing date more than 14 day after the date charged. List the crime ID, criminal

ID, date charged, and hearing date.

6. List all criminals with the zip code 23510. List the criminal ID, last name, and zip code. Sort the list by

criminal ID.

7. List all crimes that don’t have a hearing date scheduled. List the crime ID, criminal ID, date charged,

and hearing date.

8. List all sentences with a probation officer assigned. List the sentence ID, criminal ID, and probation

officer ID. Sort the list by probation officer ID and then criminal ID.

9. List all crimes that are classified as misdemeanors and are currently in appeal. List the

crime ID, criminal ID, classification, and status.

10. List all crime charges with a balance owed. List the charge ID, crime ID, fine amount, court

fee, amount paid, and amount owed.

11. List all police officers who are assigned to the precinct OCVW or GHNT and have a status

of active. List the officer ID, last name, precinct, and status. Sort the list by precinct and

then by officer last name.

FOR MY SQL:

DROP TABLE aliases CASCADE;
DROP TABLE criminals CASCADE;
DROP TABLE crimes CASCADE;
DROP TABLE appeals CASCADE;
DROP TABLE officers CASCADE;
DROP TABLE crime_officers CASCADE;
DROP TABLE crime_charges CASCADE;
DROP TABLE crimeCodes CASCADE;
DROP TABLE probOfficers CASCADE;
DROP TABLE sentences CASCADE;
DROP table prob_contact CASCADE;
CREATE TABLE aliases
(aliasId INT(6),
criminalId INT(6),
alias VARCHAR(10));
CREATE TABLE criminals
(criminalId INT(6),
last VARCHAR(15),
first VARCHAR(10),
street VARCHAR(30),
city VARCHAR(20),
state CHAR(2),
zip CHAR(5),
phone CHAR(10),
v_status CHAR(1) DEFAULT 'N', p_status CHAR(1) DEFAULT 'N' );
CREATE TABLE crimes
(crime_id INT(9),
criminalId INT(6),
classification CHAR(1),
dateCharged DATE,
status CHAR(2),
hearingDate DATE,
appealCutDate DATE);
CREATE TABLE sentences
(sentenceId INT(6),
criminalId INT(9),
type CHAR(1),
probId INT(5),
startDate DATE,
endDate DATE,
violations INT(3));
CREATE TABLE probOfficers
(probId INT(5),
last VARCHAR(15),
first VARCHAR(10),
street VARCHAR(30),
city VARCHAR(20),
state CHAR(2),
zip CHAR(5),
phone CHAR(10),
email VARCHAR(30),
status CHAR(1) DEFAULT 'A',
mgrId INT(5) );
CREATE TABLE officers
(officeId INT(8),
last VARCHAR(15),
first VARCHAR(10),
precinct CHAR(4),
badge VARCHAR(14),
phone CHAR(10),
status CHAR(1) DEFAULT 'A' );
CREATE TABLE crimeCodes
(crimeCode INT(3),
code_description VARCHAR(30));
ALTER TABLE crimes
MODIFY classification char(1) DEFAULT 'U';
ALTER TABLE crimes
ADD (dateRecorded datetime DEFAULT NOW());
ALTER TABLE probOfficers
ADD (pagerId CHAR(10));
ALTER TABLE aliases
MODIFY alias VARCHAR(20);
ALTER TABLE criminals
ADD CONSTRAINT criminals_criminalsId_pk PRIMARY KEY (criminalId);
ALTER TABLE criminals
ADD CONSTRAINT criminals_vstatus_ck CHECK (v_status IN('Y','N'));
ALTER TABLE criminals
ADD CONSTRAINT criminals_pstatus_ck CHECK (p_status IN('Y','N'));
ALTER TABLE aliases
ADD CONSTRAINT aliases_id_pk PRIMARY KEY (aliasId);
ALTER TABLE aliases
ADD CONSTRAINT appeals_criminals_criminalid_fk FOREIGN KEY (criminalId)
REFERENCES criminals(criminalId);
ALTER TABLE aliases
MODIFY criminalId int(6) NOT NULL;
ALTER TABLE crimes
ADD CONSTRAINT crimes_id_pk PRIMARY KEY (crime_id);
ALTER TABLE crimes
ADD CONSTRAINT crimes_class_ck CHECK (classification IN('F','M','O','U'));
ALTER TABLE crimes
ADD CONSTRAINT crimes_status_ck CHECK (status IN('CL','CA','IA'));
ALTER TABLE crimes
ADD CONSTRAINT crimes_criminalid_fk FOREIGN KEY (criminalId)
REFERENCES criminals(criminalId);
ALTER TABLE crimes
MODIFY criminalId int(6) NOT NULL;
ALTER TABLE probOfficers
ADD CONSTRAINT probofficers_id_pk PRIMARY KEY (probId);
ALTER TABLE probOfficers
ADD CONSTRAINT probofficers_status_ck CHECK (status IN('A','I'));
ALTER TABLE sentences
ADD CONSTRAINT sentences_id_pk PRIMARY KEY (sentenceId);
ALTER TABLE sentences
ADD CONSTRAINT sentences_crimeid_fk FOREIGN KEY (criminalId)
REFERENCES criminals(criminalId);
ALTER TABLE sentences
MODIFY criminalId int(6) NOT NULL;
ALTER TABLE sentences
ADD CONSTRAINT sentences_probid_fk FOREIGN KEY (probId)
REFERENCES probOfficers(probId);
ALTER TABLE sentences
ADD CONSTRAINT sentences_type_ck CHECK (type IN('J','H','P'));
ALTER TABLE officers
ADD CONSTRAINT officers_id_pk PRIMARY KEY (officeId);
ALTER TABLE officers
ADD CONSTRAINT officers_status_ck CHECK (status IN('A','I'));
ALTER TABLE crimeCodes
ADD CONSTRAINT crimecodes_code_pk PRIMARY KEY (crimeCode);

CREATE TABLE appeals
(appeal_id INT(5),
crime_id INT(9) NOT NULL,
filing_date DATE,
hearingDate DATE,
status CHAR(1) DEFAULT 'P',
CONSTRAINT appeals_id_pk PRIMARY KEY (appeal_id),
CONSTRAINT appeals_crimeid_fk FOREIGN KEY (crime_id)
REFERENCES crimes(crime_id),
CONSTRAINT appeals_status_ck CHECK (status IN('P','A','D')) );
CREATE TABLE crime_officers
(crime_id INT(9),
officeId INT(8),
CONSTRAINT crimeofficers_cid_oid_pk PRIMARY KEY (crime_id,officeId),
CONSTRAINT crimeofficers_crimeid_fk FOREIGN KEY (crime_id)
REFERENCES crimes(crime_id),
CONSTRAINT crimeofficers_officerid_fk FOREIGN KEY (officeId)
REFERENCES officers(officeId) );
CREATE TABLE crime_charges
(charge_id INT(10),
crime_id INT(9) NOT NULL,
crimeCode INT(3) NOT NULL,
charge_status CHAR(2),
fine_amount decimal(7,2),
court_fee decimal(7,2),
amount_paid decimal(7,2),
pay_due_date DATE,
CONSTRAINT crimecharges_id_pk PRIMARY KEY (charge_id),
CONSTRAINT crimecharges_crimeid_fk FOREIGN KEY (crime_id)
REFERENCES crimes(crime_id),
CONSTRAINT crimecharges_code_fk FOREIGN KEY (crimeCode)
REFERENCES crimeCodes(crimeCode),
CONSTRAINT crimecharges_status_ck CHECK (charge_status IN('PD','GL','NG')) );

INSERT INTO crimeCodes
VALUES (301,'Agg Assault');
INSERT INTO crimeCodes
VALUES (302,'Auto Theft');
INSERT INTO crimeCodes
VALUES (303,'Burglary-Business');
INSERT INTO crimeCodes
VALUES (304,'Criminal Mischief');
INSERT INTO crimeCodes
VALUES (305,'Drug Offense');
INSERT INTO crimeCodes
VALUES (306,'Bomb Threat');
INSERT INTO probOfficers (probId, last, first, city, status, mgrId)
VALUES (100, 'Peek', 'Susan', 'Virginia Beach', 'A', NULL);
INSERT INTO probOfficers (probId, last, first, city, status, mgrId)
VALUES (102, 'Speckle', 'Jeff', 'Virginia Beach', 'A', 100);
INSERT INTO probOfficers (probId, last, first, city, status, mgrId)
VALUES (104, 'Boyle', 'Chris', 'Virginia Beach', 'A', 100);
INSERT INTO probOfficers (probId, last, first, city, status, mgrId)
VALUES (106, 'Taps', 'George', 'Chesapeake', 'A', NULL);
INSERT INTO probOfficers (probId, last, first, city, status, mgrId)
VALUES (108, 'Ponds', 'Terry', 'Chesapeake', 'A', 106);
INSERT INTO probOfficers (probId, last, first, city, status, mgrId)
VALUES (110, 'Hawk', 'Fred', 'Chesapeake', 'I', 106);
INSERT INTO officers (officeId, last, first, precinct, badge, phone, status)
VALUES (111112, 'Shocks', 'Pam', 'OCVW', 'E5546A33', '7574446767', 'A');
INSERT INTO officers (officeId, last, first, precinct, badge, phone, status)
VALUES (111113, 'Busey', 'Gerry', 'GHNT', 'E5577D48', '7574446767', 'A');
INSERT INTO officers (officeId, last, first, precinct, badge, phone, status)
VALUES (111114, 'Gants', 'Dale', 'SBCH', 'E5536N02', '7574446767', 'A');
INSERT INTO officers (officeId, last, first, precinct, badge, phone, status)
VALUES (111115, 'Hart', 'Leigh', 'WAVE', 'E5511J40', '7574446767', 'A');
INSERT INTO officers (officeId, last, first, precinct, badge, phone, status)
VALUES (111116, 'Sands', 'Ben', 'OCVW', 'E5588R00', '7574446767', 'I');
COMMIT;
INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1020, 'Phelps','Sam','1105 Tree Lane', 'Virginia Beach', 'VA', '23510',
7576778484, 'Y', 'N');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10085, 1020, 'F', '2008-09-03', 'CA', '2008-09-15', '2008-12-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5000, 10085, 301, 'GL', 3000, 200, 40, '2008-10-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5001, 10085, 305, 'GL', 1000, 100, NULL, '2008-10-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1000, 1020, 'J', NULL, '2008-09-15', '2010-09-15', 0);
INSERT INTO aliases (aliasId, criminalId, alias)
VALUES (100, 1020, 'Bat');
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10085, 111112);
INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1021, 'Sums','Tammy','22 E. Ave', 'Virginia Beach', 'VA', '23510',
7575453390, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10086, 1021, 'M', '2008-10-20', 'CL', '2008-12-05', NULL);
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5002, 10086, 304, 'GL', 200, 100, 25, '2009-02-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1001, 1021, 'P', 102, '2008-12-05', '2009-06-05', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10086, 111114);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1022, 'Caulk','Dave', '8112 Chester Lane', 'Chesapeake', 'VA', '23320',
7578403690, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10087, 1022, 'M', '2008-10-30', 'IA', '2008-12-05', '2009-03-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5003, 10087, 305, 'GL', 100, 50, 150, '2009-03-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1002, 1022, 'P', 108, '2009-03-20', '2009-08-20', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10087, 111115);
INSERT INTO aliases (aliasId, criminalId, alias)
VALUES (101, 1022, 'Cabby');
INSERT INTO appeals (appeal_id, crime_id, filing_date, hearingDate, status)
VALUES (7500, 10087, '2008-12-10', '2008-12-20', 'A');
INSERT INTO appeals (appeal_id, crime_id, filing_date, hearingDate, status)
VALUES (7501, 10086, '2008-12-15', '2008-12-20', 'A');
INSERT INTO appeals (appeal_id, crime_id, filing_date, hearingDate, status)
VALUES (7502, 10085, '2008-09-20', '2008-10-8', 'A');
INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1023, 'Dabber','Pat', NULL, 'Chesapeake', 'VA', '23320',
NULL, 'N', 'N');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10088, 1023, 'O', '2008-11-05', 'CA', NULL, NULL);
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5004, 10088, 306, 'PD', NULL, NULL, NULL, NULL);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10088, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1025, 'Cat','Tommy', NULL, 'Norfolk', 'VA', '26503',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10089, 1025, 'M', '2008-10-22', 'CA', '2008-11-25', '2009-02-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5005, 10089, 305, 'GL', 100, 50, NULL, '2009-02-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1004, 1025, 'P', 106, '2008-12-20', '2009-03-20', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10089, 111115);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10089, 111116);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1026, 'Simon','Tim', NULL, 'Norfolk', 'VA', '26503',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10090, 1026, 'M', '2008-10-22', 'CA', '2008-11-25', '2009-02-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5006, 10090, 305, 'GL', 100, 50, NULL, '2009-02-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1005, 1026, 'P', 106, '2008-12-20', '2009-03-20', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10090, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1027, 'Pints','Reed', NULL, 'Norfolk', 'VA', '26505',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10091, 1027, 'M', '2008-10-24', 'CA', '2008-11-28', '2009-02-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5007, 10091, 305, 'GL', 100, 50, 20, '2009-02-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1006, 1027, 'P', 106, '2008-12-20', '2009-03-20', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10091, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1028, 'Mansville','Nancy', NULL, 'Norfolk', 'VA', '26505',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10092, 1028, 'M', '2008-10-24', 'CA', '2008-11-28', '2009-02-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5008, 10092, 305, 'GL', 100, 50, 25, '2009-02-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1007, 1028, 'P', 106, '2008-12-20', '2009-03-20', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10092, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1024, 'Perry','Cart', NULL, 'Norfolk', 'VA', '26501',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10093, 1024, 'M', '2008-10-22', 'CA', '2008-11-25', '2009-02-15');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5009, 10093, 305, 'GL', 100, 50, NULL, '2009-02-15');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1003, 1024, 'P', 106, '2008-12-20', '2009-03-20', 1);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10093, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1029, 'Statin','Penny', NULL, 'Norfolk', 'VA', '26505',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (10094, 1029, 'M', '2008-10-26', 'CA', '2008-11-26', '2009-02-17');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5010, 10094, 305, 'GL', 50, 50, NULL, '2009-02-17');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1008, 1029, 'P', 106, '2008-12-20', '2009-02-05', 1);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (10094, 111115);

INSERT INTO criminals (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1030, 'Panner','Lee', NULL, 'Norfolk', 'VA', '26505',
NULL, 'N', 'Y');
INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (25344031, 1030, 'M', '2008-10-26', 'CA', '2008-11-26', '2009-02-17');
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5011, 25344031, 305, 'GL', 50, 50, NULL, '2009-02-17');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1009, 1030, 'P', 106, '2008-12-20', '2009-02-05', 1);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (25344031, 111115);

INSERT INTO crimes (crime_id, criminalId, classification, dateCharged, status,
hearingDate, appealCutDate)
VALUES (25344060, 1030, 'M', '2008-11-18', 'CL', '2008-11-26', NULL);
INSERT INTO crime_charges(charge_id, crime_id, crimeCode, charge_status,
fine_amount, court_fee, amount_paid, pay_due_date)
VALUES (5012, 25344060, 305, 'GL', 50, 50, 100, '2009-02-17');
INSERT INTO sentences (sentenceId, criminalId, type, probId, startDate,
endDate, violations)
VALUES (1010, 1030, 'P', 106, '2009-02-06', '2009-07-06', 0);
INSERT INTO crime_officers (crime_id, officeId)
VALUES (25344060, 111116);
COMMIT;


CREATE TABLE prob_contact
(prob_cat INT(2),
low_amt INT(5),
high_amt INT(5),
con_freq VARCHAR(20) );
INSERT INTO prob_contact
VALUES(10, 1, 80, 'Weekly');
INSERT INTO prob_contact
VALUES(20, 81, 160, 'Every 2 weeks');
INSERT INTO prob_contact
VALUES(30, 161, 500, 'Monthly');
COMMIT;

CREATE TABLE criminals_dw
(criminalId INT(6),
last VARCHAR(15),
first VARCHAR(10),
street VARCHAR(30),
city VARCHAR(20),
state CHAR(2),
zip CHAR(5),
phone CHAR(10),
v_status CHAR(1),
p_status CHAR(1) );
INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1020, 'Phelps','Sam','1105 Tree Lane', 'Virginia Beach', 'VA', '23510',
7576778484, 'Y', 'N');
INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1021, 'Sums','Tammy','22 E. Ave', 'Virginia Beach', 'VA', '23510',
7575453390, 'N', 'Y');
INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1022, 'Caulk','Dave', '8112 Chester Lane', 'Chesapeake', 'VA', '23320',
7578403690, 'N', 'Y');
INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1023, 'Dabber','Pat', NULL, 'Chesapeake', 'VA', '23320',
NULL, 'N', 'N');
INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1024, 'Perry','Cart', '11 New St.', 'Surry', 'VA', '54501',
NULL, 'N', 'Y');
INSERT INTO criminals_dw (criminalId, last, first, street, city, state, zip, phone, v_status, p_status)
VALUES (1025, 'Cat','Tommy', NULL, 'Norfolk', 'VA', '26503',
7578889393, 'N', 'Y');
COMMIT;

engineering   Computer-Science   
0 0
Add a comment Improve this question Transcribed image text
Next > < Previous
Sort answers by oldest

Homework Answers

Answer #1

Answer)

1. List all criminal aliases beginning with the letter B.

select alias from aliases where alias like 'B%';

Output:

Bat

2. List all crimes that occurred (were charged) during the month October 2008. List the crime ID, criminal ID, date charged, and classification.

select crime_id, criminalId, dateCharged, classification from crimes where dateCharged like '2008-10%';

Output:

10086|1021|2008-10-20|M

10087|1022|2008-10-30|M

10089|1025|2008-10-22|M

10090|1026|2008-10-22|M

10091|1027|2008-10-24|M

10092|1028|2008-10-24|M

10093|1024|2008-10-22|M

10094|1029|2008-10-26|M

25344031|1030|2008-10-26|M

3. List all crimes with a status of CA (can appeal) or IA (in appeal). List the crime ID, criminal ID, charged, and status.

select crime_id, criminalId, dateCharged, status from crimes where status in('CA','IA');

Output:

10085|1020|2008-09-03|CA

10087|1022|2008-10-30|IA

10088|1023|2008-11-05|CA

10089|1025|2008-10-22|CA

10090|1026|2008-10-22|CA

10091|1027|2008-10-24|CA

10092|1028|2008-10-24|CA

10093|1024|2008-10-22|CA

10094|1029|2008-10-26|CA

25344031|1030|2008-10-26|CA

4. List all crimes classified as a felony. List the crime ID, criminal ID, date charged, and classification

select crime_id, criminalId, dateCharged, status from crimes where classification = 'F';

Output:

10085|1020|2008-09-03|CA

0 0
Add a comment
Know the answer?
Add Answer to:
Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Log In

Sign Up

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions

  • Create three or more MySQL Data Control language (DCL) Statements using the Youth League Database. 1....

    Create three or more MySQL Data Control language (DCL) Statements using the Youth League Database. 1. A Create new user statement for the database 2. A statement that grants privileges to the new user 3. A statement that revokes privileges 1. A SQL Text File containing the SQL commands to create the database, create the table, and insert the sample test data into the table. 2. A SQL Text File containing the DCL SQL Statements. eted hemas Untitled Limit to...

  • 2. Examine the SQL Script for this test.  Explain the purpose of each of the stored procedures...

    2. Examine the SQL Script for this test.  Explain the purpose of each of the stored procedures listed below.   Test2SP1 Test2SP2 Test2SP3 Test2SP4 Test2SP5 Test2SP6 DATABASE CODE: if db_id('Test2PremierProducts') is not null begin use master alter database Test2PremierProducts set SINGLE_USER with rollback immediate drop database Test2PremierProducts end create database Test2PremierProducts go USE Test2PremierProducts; go /* create the tables */ CREATE TABLE Rep (RepNum int Identity(10,10), LastName VARCHAR(15), FirstName VARCHAR(15), Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Commission MONEY, Rate DECIMAL(3,2),...

  • * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...

    * myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...

  • Oracle 12c SQL - Chapter 5: Case Study: City Jail, Part II Note: You are limited...

    Oracle 12c SQL - Chapter 5: Case Study: City Jail, Part II Note: You are limited to using the Oracle Live interface to create solutions for the problems below. SQL Live does not allow as many commands, statements, and symbols as full Oracle SQL. Specifically, the ampersand substitution variable symbol (&) may not be used for any of the solutions below because Oracle Live does not recognize it. Oracle Live is available to use here: https://livesql.oracle.com/apex/f?p=590:1000 Execute the CityJail_5.sql script...

  • Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and...

    Database HW help!!!!!!! Provide the SQL statements for the following queries. (1) Provide the names and phones of all swimmers currently in level (of id) 3. +-------+---------+---------------------------+ | FName | LName   | EMail                     | +-------+---------+---------------------------+ | Bobby | Khan    | theBKhan1 | | Clara | Johnson | ClaraJohnson_11 | +-------+---------+---------------------------+ 2 rows in set (0.00 sec) (2) Provide the names of swimmers who have signed up to participate in the event '100M Butterfly' of Meet id 1. +-------+---------+ | FName...

  • --Create procedure for GetPartNums in SQL --GetPartNums- retrieve all part nums from the database --Database if...

    --Create procedure for GetPartNums in SQL --GetPartNums- retrieve all part nums from the database --Database if db_id('TestPremierProducts') is not null begin use master alter database TestPremierProducts set SINGLE_USER with rollback immediate drop database TestPremierProducts end create database TestPremierProducts go USE TestPremierProducts; go /* create the tables */ CREATE TABLE Rep (RepNum int Identity(1,1), LastName VARCHAR(15), FirstName VARCHAR(15), Street VARCHAR(15), City VARCHAR(15), State VARCHAR(2), Zip VARCHAR(5), Commission MONEY, Rate DECIMAL(3,2), PRIMARY KEY (RepNum)); go CREATE TABLE Customer (CustomerNum int Identity(1,1) PRIMARY...

  • I need help with the following SQL query for a company database (script given below). The...

    I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...

  • These question is for mysql, so I want know what is answer that is information for...

    These question is for mysql, so I want know what is answer that is information for source: DROP DATABASE IF EXISTS travel; CREATE DATABASE travel; USE travel; -- -- Table structure for table `equipment` -- DROP TABLE IF EXISTS `equipment`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `equipment` ( `EquipID` int(11) NOT NULL DEFAULT '0', `EquipmentName` varchar(50) NOT NULL DEFAULT '', `EquipmentDescription` varchar(100) NOT NULL DEFAULT '', `EquipmentCapacity` int(11) NOT NULL DEFAULT '0',...

  • Program: MYSQL (please show screenshots of the solution)  Ill give good rating! Define a view named TopLevelCust...

    Program: MYSQL (please show screenshots of the solution)  Ill give good rating! Define a view named TopLevelCust that consists of the number, name, address, balance and credit limit of all customers with credit limits that are greater than or equal to $5,000. Address is defined as street, city, state and zip. Concatenate these into one field named Address.   A. Type in your view construct here: B. Write a query to retrieve the number and name of all customers in the TopLevelCust...

  • SQL I have a database CREATE TABLE vendor ( vid CHAR(2) NOT NULL, vname VARCHAR(25) NOT...

    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)...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT