1. Find the name of the Database you are using. Query Goes Here:…
Question Answered step-by-step 1. Find the name of the Database you are using. Query Goes Here:… 1. Find the name of the Database you are using.Query Goes Here: STATUS;Output Goes Here: 2. Find the name of the Database using parameter @id.Query Goes Here:SELECT DB_NAME(2) AS Database_Name;Output Goes Here: 3. Create a stored procedure that returns all officers (first and last names supplied), their precinct, badge, and their status. This procedure does not use any parameters. Query Goes Here:CREATE PROCEDURE officersASBEGIN SELECT first name, last names, precinct, badge, statusFROM officers ENDOutput Goes Here: 4. Create a procedure that returns two result sets:a. Top 10 rows showing officer id, his first and last nameb. Top 10 rows showing crime id, and its classification.Query Goes Here:CREATE PROCEDURE officersASBEGIN SELECT officers id,first_name, last_nameFROM officers SELECT crime id,classificationFROM crime ENDOutput Goes Here: 5. Create a stored procedure that returns information for a specific criminal by passing values for the criminal’s first name and last name. This procedure accepts only exact matches for the parameters passed.Query Goes Here:CREATE PROCEDURE SelectCriminalInformation @Firstname, @LastnameASSELECT * FROM Criminals WHERE Firstname = @Firstname AND Lastname = @LastnameGO; Output Goes Here: 6. Create a stored procedure that returns information for criminals by passing full or partial values for the criminal’s first name and last name. This procedure pattern matches the parameters passed or, if not supplied, uses the preset default (last names that start with the letter D).Query Goes Here:CREATE PROCEDURE GetCriminalDetails @first nvarchar(10) = NULL, @last nvarchar(15) = NULLAS BEGIN IF @first is NULL OR @last is NULL BEGIN SELECT * from criminals WHERE last like ‘D%’; RETURN END SELECT * from criminals where first like ‘%’ + @first + ‘%’ AND last like ‘%’ + @last + ‘%’ ;END Output Goes Here: ___________________________________________________________________________________________________________________________ CITY JAIL SQL DATABASE: Image transcription textuse [City Jail DB] CREATE TABLE aliases (m INT, criminalid INT, alias VARCHAR(10)),’ CREATE TABLE criminals(criminal id INT, last VARCHAR(15), first VAR… Show more… Show moreImage transcription textstart date DATE, end date DATE, violations INT); CREATETABLE prob officers (prob_id INT, last VARCHAR(15), firstVARCHAR(10), street VARCHAR(30), city V… Show more… Show moreImage transcription textPage 3 ADD date_recorded DATE DEFAULT getDate();ALTER TABLE prob officers ADD pager# CHAR(10);ALTER TABLE aliases ALTER COLUMN alias … Show more… Show moreImage transcription textPage 4 ADD CONSTRAINT crimes status_ck CHECK(status IN(‘CL’, ‘CA’, ‘IA’)); ALTER TABLE crimes ADDCONSTRAINT crimes criminalid fk FOREIGN… Show more… Show moreImage transcription textPage 5 ALTER COLUMN crime code INT NOT NULL;ALTER TABLE crime codes ADD CONSTRAINT crimecodescode_pk PRIMARY KEY (crime_code); CREAT… Show more… Show moreImage transcription textPage 6 amount paid INT, pay due date DATE, CONSTRAINTcrimecharges_id_pk PRIMARY KEY (charge_id),CONSTRAINT crimecharges_crimeid_fk FORE… Show more… Show moreImage transcription textPage 7 INSERT INTO prob officers (prob id, last, first, city,status, mgrid) VALUES (108, ‘Ponds’, ‘Terry’, ‘Chesapeake’,’A’, 106); INSERT INTO prob officers (prob id, … Show more… Show moreImage transcription textPage 8 VALUES (1000, 1020, ‘J’, NULL, ’15-SEP-08′,’15-SEP-10′, 0); INSERT INTO aliases (alias id, criminal id,alias) VALUES (100, 1020, ‘Bat’); INSERT INTO… Show more… Show moreImage transcription textPage 9 INSERT INTO sentences (sentence id, criminal id,type, prob id, start date, end date, violations) VALUES(1002, 1022, ‘P’, 108, ’20-MAR-09′, ’20-AUG-… Show more… Show moreImage transcription textPage 10 hearing date, appeal cut date) VALUES (10089,1025, ‘M’, ’22-OCT-08′, ‘CA’, ’25-NOV-08′, ’15-FEB-09′);INSERT INTO crime charges(charge id, crime … Show more… Show moreImage transcription textPage 11 VALUES (1027, ‘Pints” ‘Reed’, NULL, ‘Norfolk’,’VA’, ‘26505’, NULL, ‘N’, ‘Y’); INSERT INTO crimes (crime id,criminal id, classification, date_charged, status… Show more… Show moreImage transcription textPage 12 INSERT INTO criminals (criminal id, last, first,street, city, state, zip, phone, v_status, p status) VALUES(1024, ‘Perry’ “Cart’, NULL, ‘Norfolk’, ‘VA’, … Show more… Show moreImage transcription textPage 13 INSERT INTO criminals (criminal id, last, first,street, city, state, zip, phone, v status, p status) VALUES(1030, ‘Panner’ ‘Lee’, NULL, ‘Norfolk’, ‘VA’, ’26… Show more… Show moreImage transcription textPage 14 CREATE SEQUENCE appeals id sea START WITH7505 NO CACHE NO CYCLE; CREATE TABLE prob contact(prob_cat INT, low amt INT, high_amt INT, con f… Show more… Show moreImage transcription textPage 15 INSERT INTO criminals_dw (criminal id, last, first,street, city, state, zip, phone, v status, p status) VALUES(1020, ‘Phelps’, ‘Sam’,’1105 Tree Lane’, ‘Virginia… Show more… Show more Computer Science Engineering & Technology MYSQL CEN 4333C Share QuestionEmailCopy link Comments (0)
