Search results for 'SELECT'

DAT/305 Week 3 Challenge Activities 3.6.1: Hash tables with modulo hash function

CHALLENGE ACTIVITY 1

A hash table with non-negative integer keys has a modulo hash function of key % 20.

Hash function index range: 0 to

Item 128 will go in bucket

CHALLENGE ACTIVITY 2

An ordinary array contains items with keys: 30, 22, 6, 34, 2, 10,13
A hash table has 15 buckets with hash function key % 15.
The hash table contains the same items as the array.

How many elements are checked when searching for each of the following items?

CHALLENGE ACTIVITY 3

An empty hash table hashTable has 5 buckets and a hash function of key % 5.
The following operations are performed in order.
Select which operations cause a collision.
Hashinsert(hash Table, 2)
Hashinsert(hashTable, 12)
Hashinsert(hashTable, 0)
Hashinsert(hashTable, 5)
Hashinsert(hash Table, 15)

DAT/380 Week 4 LAB 4.7 - Select number of movies grouped by year

The Movie table has the following columns:
ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer

Write a SELECT statement to select the year and the total number of movies for that year.

Hint: Use the COUNT() function and GROUP BY clause

DAT/380 Week 4 LAB 4.6 - Select lesson schedule with inner join

The database has three tables for tracking horse-riding lessons:
Horse with columns:
ID - primary key
RegisteredName
Breed
Height
BirthDate

Student with columns:
ID - primary key
FirstName
LastName
Street
City
State
Zip
Phone
EmailAddress

LessonSchedule with columns:
HorseID - partial primary key, foreign key references Horse(ID)
StudentID - foreign key references Student(ID)
LessonDateTime - partial primary key

Write a SELECT statement to create a lesson schedule with the lesson date/time, horse ID, and the student's first and last names. Order the results in ascending order by lesson date/time, then by horse ID. Unassigned lesson times (student ID is NULL) should not appear in the schedule.

Hint: Perform a join on the Student and LessonSchedule tables, matching the student IDs.

DAT/380 Week 3 LAB 3.16 - Select horses with logical operators

The Horse table has the following columns:
ID - integer, primary key
RegisteredName - variable-length string
Breed - variable-length string
Height - decimal number
BirthDate - date

Write a SELECT statement to select the registered name, height, and birth date for only horses that have a height between 15.0 and 16.0 (inclusive) or have a birth date on or after January 1, 2020.

DAT/380 Week 2 LAB 2.10 - Select employees and managers with inner join

The Employee table has the following columns:
ID - integer, primary key
FirstName - variable-length string
LastName - variable-length string
ManagerID - integer
Write a SELECT statement to show a list of all employees' first names and their managers' first names. List only employees that have a manager. Order the results by Employee first name. Use aliases to give the result columns distinctly different names, like "Employee" and "Manager".

Hint: Join the Employee table to itself using INNER JOIN.

DAT/380 Week 2 LAB 2.9 - Select movie ratings with left join

The Movie table has the following columns:
ID - integer, primary key
Title - variable-length string
Genre - variable-length string
RatingCode - variable-length string
Year - integer
The Rating table has the following columns:

Code - variable-length string, primary key
Description - variable-length string
Write a SELECT statement to select the Title, Year, and rating Description. Display all movies, whether or not a RatingCode is available.

Hint: Perform a LEFT JOIN on the Movie and Rating tables, matching the RatingCode and Code columns.

DBM 405A Lab 2 SQL Review

I. OBJECTIVES
1. Get access to a MySQL environment.
2. Familiarize yourself with the environment.

II. PARTS LIST
3. EDUPE Omnymbus Environment (https://devry.edupe.net:8300); and/or
4. MySQL (dev.mysql.com/downloads).

III. PROCEDURE
You may do the labs in this class on your own computer equipped with MySQL or you may use the MySQL environment hosted by the vendor Omnymbus. You may even use both.

Using MySQL on your own computer means downloading a version from dev.mysql.com/downloads and installing the database engine on your computer. This can be done easily and there are a number of resources on the web to help you with this process.

You may also use the MySQL environment hosted by Omnymbus by logging in with your credentials to https://devry.edupe.net:8300. 

Using the first approach will give you experience writing programs on a desktop computer and accessing a database. Using the second approach will give you experience writing applications that access a database in a cloud environment.

You are encouraged to use both approaches.

In the previous lab (Week 1), you have established a database environment (either MySQL Community Server or the Omnymbus environment). You may continue to use that environment or you my return to Week 1 to set up the alternate environment.

Lab Procedure Continued (common to both environments)
1. Use the database named “Baseball” created in last week’s lab.
2. Use the table named “Teams” which consists of the following fields:
a. TeamCode (character 5)
b. TName (character 20)
c. TAddress (character 30)
d. TCity (character 20)
e. TState (character 2)
f. TZip (character 9)
g. Wins (integer)
h. Losses (integer)
3. You should have a table with 8 teams.
4. Use the table named “Players”, which consists of the following fields:
a. FirstName (character 20)
b. LastName (character 30)
c. MidInit (character 1)
d. PAddress (character 30)
e. PCity (character 20)
f. PState (character 2)
g. PZip (character 9)
h. Phone (character 11)
i. Team (character 5)
5. You should have a table with 80 players (10 on each team). Players are identified by the Team field which matches the TeamCode in the Teams table.
6. Demonstrate the use of the WHERE clause by selecting and displaying only the players from one team.
7. Demonstrate the use of the COUNT function by determining how many players are on each team and displaying the results.
8. For each City, find the number of players who list their home in that city.
9. Demonstrate the ORDER BY clause by listing all the players in alphabetical order by last name within Team.

Murachs MySQL 3rd Edition Chapter 15 Exercise 1 and 2

1. Write a script that creates and calls a stored procedure named insert_glaccount. First, code a statement that creates a procedure that adds a new row to the General_Ledger_Accounts table in the AP schema. To do that this procedure should have two parameters, one for each of the two columns in this table. Then, code a CALL statement that tests this procedure. (Note that this table doesn't allow duplicate account descriptions.)

2. Write a script that creates and calls a stored function named test_glaccounts_description. First, create a function that tests whether an account description is already in the General_Ledger_Accounts table. To do that, this function should accept one parameter for the account description, and it should return a value of 1 if the account description is in the table or 0 if it isn't. (Note: If a SELECT statement doesn't return any data, it raises a NOT FOUND condition that your function can handle.)

ITSE 2309 LAB 3 Normalization and Creating Tables

(For this Lab -there are various programs that can be used -- there is MS Excel, where by drawing boxes and using the arrows /lines option the graphics can be accomplished - there is also MS Visio, if available and the faithful – pencil/pen/ruler and paper - ( which may require the use of a scanner for submission )

1. Using the table description and business rules listed below answer or perform the following:
a. Identify the Primary Key of the table as it is currently shown.
b. Identify all of the functional dependencies.
c. Draw the dependency diagram for the table(s)
( Hint: 1NF see pages 197-198 in Database System text book)

2. Normalize the relation to 3rd Normal Form (3NF).
a. List the normalized tables using the standard table notation (remember Chen and/or Crow’s foot)
- Tablename (Col1, Col2……Coln)
  Primary Key:
  Foreign Key:
b. Draw the dependency diagrams for each of the tables.( Hint: you should have no less than four tables)
c. Provide an E-R Diagram of the tables to be created

3. Using the CREATE TABLE command, create each of the normalized tables. Run a DESCRIBE command for each table.
- Include the PRIMARY KEY constraint for each table.
- Include the FOREIGN KEY constraint for each table to which it applies.
- Include the NOT NULL constraint for Student Name and Instructor Name.
- Include the CHECK constraint for the Grade to ensure it is one of the 5 acceptable values (A, B, C, D, or F).

4. Code INSERT commands to insert the data from the attached page into the tables you created in 2.
- Run 'SELECT * FROM tablename;' commands to check the contents of your tables.

5. Your submittal/output to hand in should include: ( with no less than ten pages)
- Question 1, part a.: Primary Key columns
- Question 1, part b.: Dependency Diagram
- Question 2, Part a.: List of normalized table descriptions
- Question 2, Part b.: Dependency diagrams for the normalized tables
- Question 2, Part c.: E-R Diagram of the tables to be created( Chen and Crow’s feet)
- Question 2 Part d.: UML Diagrams of the tables to be created (See pages 143,144, )
- Question 3: Print out of the CREATE TABLE commands & results.
- Question 3: Print out of the DESCRIBE table commands & resulting SQL message.
- Question 4: Print out of the INSERT commands & Resulting SQL message.
- Question 4: Print out of the SELECT * FROM tablename listing and SQL results.

STUDENT TABLE
Student Student Student Student Course Course Instructor Instructor Instructor Stu_Crse
ID Name Address Major ID Title ID Name Office Grade
268300458 Williams 208 Brooks CIS CIS 350 Database 301 Codd B104 A
268300458 Williams 208 Brooks CIS CIS 465 Systems Anal 451 Parsons B317 B
543291073 Baker 104 Philips Acct CIS 350 Database 301 Codd B104 C
543291073 Baker 104 Philips Acct Acc 201 Fund of Acctg. 255 Miller H310 B
543291073 Baker 104 Philips Acct Mkt 300 Into to Mktg 518 Bennett B212 A
695381127 White 208 Brooks Math Mth 202 College algebra 622 Hilbert M301 B
695381127 White 208 Brooks Math Acc 201 Fund of Acctg 255 Miller H310 A

Business Rules:
( see page 239 in Database Systems Text Figure 7.1)
- Only one class is taught for each course ID.
- Students may take up to 4 courses.
- Each course may have a maximum of 25 students.
- Each course is taught by only one Instructor.
- Each student may have only one major.