rider_student
Column Data Type Description
student_id integer the primary key
first_name varchar(25) student first name
last_name varchar(25) student last name
major_id integer the ID of the student's major; a foreign key for
the major_id in the rider_major table
rider_major
Column Data Type Description
major_id integer the primary key
major_name varchar(50) student first name
major_description varchar(100) student last name
Use the Tables above to answer the questions.
Questions:
1. Write a SQL statement to add a student record to the
rider_student table for you. This should be a student with your
name. Add data to all table columns. Examine the records in the
rider_major table and use a major currently in the table.
2. Write a SQL statement to add a major record for a major not
currently listed in the rider_major table. Add data to all table
columns. Be creative and assume you can create a major for anything
of interest to you. You must a major that does not currently exist
to the table.
3. Assume you want to change your major to the major you added in
the previous question. Write a SQL statement to change the
major.
4. Write a SQL statement to retrieve the student first_name,
last_name, major_name, and major_description for all records in the
rider_student table. This will require an appropriate join between
therider_student and rider_major tables.
5. Write a SQL statement to retrieve the student first_name,
last_name, major_name, and major_description for the rider_student
record you added for yourself. This will require an appropriate
join between therider_student and rider_major tables.
6. Add an additional record to the rider_student table (see question 1 above). Use any name. Then write a delete statement to delete that record. Note: you are deleting only one record and you need to write your delete statement correctly to do that.
rider_student Column Data Type Description student_id integer the primary key first_name varchar(25) student first name last_name...
Lesson 10Create the DEPARTMENT tables based on the following: Column Name ID Name Data Type Number Varchar2 Length 7 25 Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need. Create the EMPLOYEE table based on the following table chart: Column Name ID LAST_NAME FIRST_NAME DEPT_ID Data Type Number Varchar2 Varchar2 Number Length 7 25 25 7 Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification. Confirm that both the...
create table candidate ( cand_id varchar(12) primary key, -- cand_id name varchar(40) -- cand_nm ); create table contributor ( contbr_id integer primary key, name varchar(40), -- contbr_nm city varchar(40), -- contbr_city state varchar(40), -- contbr_st zip varchar(20), -- contbr_zip employer varchar(60), -- contbr_employer occupation varchar(40) -- contbr_occupation ); create table contribution ( contb_id integer primary key, cand_id varchar(12), --...
CREATE TABLE DEPT ( DEPTNO INTEGER NOT NULL, DNAME VARCHAR(14), LOC VARCHAR(13), PRIMARY KEY (DEPTNO)); INSERT INTO DEPT VALUES (10,'SPORTS','NEW YORK'); INSERT INTO DEPT VALUES (20,'HOME','DALLAS'); INSERT INTO DEPT VALUES (30,'OUTDOOR','CHICAGO'); INSERT INTO DEPT VALUES (40,'CLOTHING','BOSTON'); CREATE TABLE EMP ( EMPNO INTEGER NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INTEGER, SAL FLOAT, COMM FLOAT, DEPTNO INTEGER NOT NULL, FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO), FOREIGN KEY (MGR) REFERENCES EMP(EMPNO), PRIMARY KEY (EMPNO)); INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL, 5000,NULL,10); INSERT INTO...
Overview: Database management plays an integral role in nearly every area of business. Databases house customer, accounting, and employee data, and these different data sets must all be efficiently managed in order to make the data accessible. Companies rely on database engineers to ensure that their records are accurate, updated, and tracked in real time. This course covers structured query language (SQL) and how it can be used to manage database schemas, manipulate data, and analyze data. For your final...
True or False questions: 1. A data type mismatch error would occur when the primary key data data type is auto number and the foreign key in the related table is number. 2. The data type long integer is the same as integer. 3. When using SQL, the SELECT clause isolates tables in the database. 4. A Referential Integrity violation occur when the primary key is null. 5. When an REA model has 7 entities ,2 linking tables, 3 Resource...
Regrettably, the company you work for did not hire a student from this class to design their database. You are given the following table definitions. You (hopefully) take one look and are amazed at how bad the design decisions are in these two tables alone. Amazingly the database seems to be working OK. Propose a new way to implement these tables, list and explain the changes you make including why the old way was bad and your new change is...
Step 1: Create table audits via triggers The system must log any insertion, deletion, or updates to the following tables: • Employee table (project 1) create table Employee ( empNumber char(8) not null, firstName varchar(25) null, lastName varchar(25) null, ssn char(9) null, address varchar(50) null, state char(2) null, zip char(5) null, jobCode char(4) null, dateOfBirth date null, certification bit null, salary money null, constraint PK_EMP PRIMARY KEY(empNumber), constraint EMP_STATECHECK CHECK(state in ('CA','FL')) ) GO • Job table (project 1) create...
SQL Query Question: I have a database with the tables below, data has also been aded into these tables, I have 2 tasks: 1. Add a column to a relational table POSITIONS to store information about the total number of skills needed by each advertised position. A name of the column is up to you. Assume that no more than 9 skills are needed for each position. Next, use a single UPDATE statement to set the values in the new...
Need help writing SQL statements. The questions involved use the following database Student SID – Integer – Primary Key SName - String Address - String Classes ClassID – Integer – Primary Key ClassName - String Credits - Integer Grades SID – Integer – Primary Key CID – Integer – Primary Key Grade - Integer Q1 – Write a script that creates a view to show all the students and all the classes they took and the grades they got in...
ERD Primary Key Problems A partial ERD diagram for the student registration process is depicted below. Examples of the in- formation desired to be tracked for each entity is included below the title of the entity. List ALL of the tables you would need to include in your database for this portion of the ERD diagram. Also list a Primary Key(s) and any Foreign Key(s) that would be included in each table (if there would be no Foreign Key, write...