DBMS - CPSC 4620 Midterm | Que & answ

DBMS - CPSC 4620 Midterm | Que & answ




Write a mySQL statement DESCRIBE pet;

that can print out the

structure of table pet.

Please write a SQL INSERT INTO pet VALUES ('Winnie','John','Bear','m','1990-04-

statement to insert a 08',NULL);

record into the table "pet"

with the following values:

Name: Winnie, Owner:

John, Species: Bear, Sex:

m, Birth: 1990-04- 08,

Death: Null

Write an SQL statement to SELECT name, owner, birth

list the name, owner, and FROM pet WHERE sex = 'm'

birth date of the male pets and birth > '1990-01-01';

whose birth dates are after

1990-01-01.

Write an SQL statement to SELECT name, species, birth FROM pet ORDER BY birth DESC

show the name, species, LIMIT 1;

and birth date pf the

youngest pet in the table

pet.

,Write an SQL statement SELECT name, owner, birth

using mySQL pattern FROM pet WHERE name

matching to show name, LIKE 'B%Y';

owner, and birth date of

the pets whose names

start with 'B' and end with

'Y' in table pet.

Write an SQL statement SELECT name, owner, birth

using regular expression FROM pet WHERE name

pattern matching to show RLIKE '^F.*[yr]$';

name, owner, and birth

date of the pets whose

names start with 'F' and

end with 'y' or 'r' in table

pet.

Write an SQL statement to SELECT species, count(*) AS

query table pet to show num FROM pet GROUP BY

species and the number of species;

pets in each species as

num.

Write an SQL statement to SELECT pet.name, pet.owner, event.date,

sidplay the name, owner, event.remark FROM pet inner join event ON

ecebt date, and remarks of pet.name = event.name

pets that had "litter" after WHERE event.type='litter' AND event.date > '1995-01-01';

1995-01-01.

Write an SQL statement to UPDATE pet SET sec='m' WHERE name = 'Whistler';

change the sex of pet

, named "Whistler" to "m".

Write an SQL statement to DELETE FROM pet WHERE species='dog';

remove all dogs from table

pet.

Use only one UNIX mysql- h mysql.cs.clemson.edu -u usr -ppass menagerie

command to log into

database "menagerie"

from a terminal window

opened on machine

"trusty2.cs.clemson.edu",

assuming mySQL client is

installe on

"trusty2.cs.clemson.edu"

already, your username is

"usr" and password is

"pass".

After you log into a CREATE TABLE pet (name varchar(20), owner

database, you will enter the varchar(20), species varchar(20), sex char(1), birth

mySQL command line DATE, death DATE);

interface. Now, you want to

create a table "pet". The

type for fields "name",

"owner", and "species" is

character string with length

varying from 1 to 20

characters. The field "sex"

is CHAR type with length 1.

The type for fields "birth"

and "death" is DATE. Please