Stored Procedures and User$-$defined functions

Part One:

$1.$ Find the name of the Database you are using.

SQL$/$MySQL Code:

Screenshot:

$2.$ Find the name of the Database using parameter @id

SQL$/$MySQL Code:

Screenshot:

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

SQL$/$MySQL Code:

Screenshot:

$4.$ Create a procedure that returns two result sets:

a$.$ Top $10$ rows showing officer id$,$ his first and last name

b$.$ Top $10$ rows showing crime id$,$ and its classification.

SQL$/$MySQL Code:

Screenshot:

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

SQL$/$MySQL Code:

Screenshot:

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

SQL$/$MySQL Code:

Screenshot:

Part Two:

You must create your own functions $($user$-$defined$).$

$1.$ Display code description that has crime code from $302$ to $305,$ display both columns.

SQL$/$MySQL Code:

Screenshot:

$2.$ Display the lowest fine amount charged to a crime. Give the column an alias name

$$Lowest Fine Amount$$

SQL$/$MySQL Code:

Screenshot:

$3.$ Write a query to return the current date along with time in MS SQL Server.

SQL$/$MySQL Code:

Screenshot:

$4.$ Add $10$ days to the pay due date of the crime$\_$charges table then display the result, name

the column New$\_$Due$\_$date.

SQL$/$MySQL Code:

Screenshot:

$5.$ Display crime id and calculate how many days between filing and hearing dates, name

this column $$Days to Hearing$$

SQL$/$MySQL Code:

Screenshot:

$6.$ Display officer id along with their last names and the last five characters of their badges.

SQL$/$MySQL Code:

Screenshot:

$7.$ Write a query to display the probation officers$$ id and their first and last names separated

by comma in upper case letters, name the column O

SQL$/$MySQL Code:

Screenshot: