Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Using TinyCollege database, write the sql queries to answer each of the following questions. Your answer should consist of the SQL query and also the

Using TinyCollege database, write the sql queries to answer each of the following questions. Your answer should consist of the SQL query and also the result of the query in a table form.

  1. Show all student number first name, last name, hire date all concatenated in one column.
  2. Show the student date of birth (stu_dob), the birth year, month and day in separate columns.
  3. Show the students attributes, and the dob according to format code 102. Format code 102 show the date as follows yyyy.mm.dd
  4. Show all students born between 1970 and 1975. Use the between operator. Show their first and last name in capital letters.
  5. Show the first 3 characters of the course description.
  6. Show the students that do not have initials.
  7. shows student num, last and first name, the birth year and their age. Age is calculated based on the difference between today's date (use getdate() function) and stu_dob. Use datediff() function to calculate the differencehttps://www.w3schools.com/sql/func_sqlserver_datediff.asp.


*DATABASE TO BE USED IN PICTURE BELOW*

imageimage

/* Created with SQL Script Builder v.1.5 */ /* Type of SQL SQL Server */ use TinyCollege; /* Created with SQL Script Builder v.1.5 */ CREATE TABLE COURSE ( CRS_CODE varchar(10) not null, DEPT_CODE varchar(10) not null, CRS_DESCRIPTION varchar (35), ........... CRS CREDIT numeric (8), PRIMARY KEY (CRS_CODE) ); INSERT INTO COURSE VALUES ('ACCT-211', 'ACCT', 'Accounting I', '3'); INSERT INTO COURSE VALUES ('ACCT-212', 'ACCT', 'Accounting II', '3'); INSERT INTO COURSE VALUES ('CIS-220', 'CIS','Intro. to Microcomputing','3'); INSERT INTO COURSE VALUES ('CIS-420', 'CIS', 'Database Design and Implementation', '4'); ', '4'); INSERT INTO COURSE VALUES ('QM-261', 'CIS', 'Intro. to Statistics', '3'); INSERT INTO COURSE VALUES ('QM-362', 'CIS', 'Statistical Applications', /* Created with SQL Script Builder v.1.5 */ CREATE TABLE CLASS ( CLASS_CODE varchar(5), CRS_CODE varchar(10) not null, CLASS SECTION varchar (2) not null, ........... CLASS TIME varchar(20), CLASS ROOM varchar(8), PROF_NUM numeric (8), PRIMARY KEY (CLASS_CODE), FOREIGN KEY (CRS_CODE) REFERENCES COURSE (CRS_CODE) ); 1 I INSERT INTO CLASS VALUES ('10012', 'ACCT-211', '1', 'MWF 8:00-8:50 a.m. ' 'BUS311', '105'); INSERT INTO CLASS VALUES ('10013', 'ACCT-211', '2', 'MWF 9:00-9:50 a.m.' .', 'BUS200', '105'); INSERT INTO CLASS VALUES ('10014', 'ACCT-211', '3', 'TTh 2:30-3:45 p.m.', 'BUS252', '342'); INSERT INTO CLASS VALUES ('10015', 'ACCT-212', '1', 'MWF 10:00-10:50 a.m. 1 'BUS311', '301'); INSERT INTO CLASS VALUES ('10016', 'ACCT-212', '2', 'Th 6:00-8:40 p.m. ', 'BUS252', '301'); INSERT INTO CLASS VALUES ('10017', 'CIS-220', '1', 'MWF 9:00-9:50 a.m.' 'KLR209', '228'); INSERT INTO CLASS VALUES ('10018', 'CIS-220' 'MWF 9:00-9:50 a.m. ', 'KLR211' '114'); INSERT INTO CLASS VALUES ('10019 'CIS-220', '3', 'MWF 10:00-10:50 a.m. ', 'KLR209', '228'); INSERT INTO CLASS VALUES ('10020 'CIS-420', '1', 'W 6:00-8:40 p.m. ', 'KLR209', '162'); INSERT INTO CLASS VALUES ('10021', 'QM-261', '1', 'MWF 8:00-8:50 a.m.', 'KLR200', '114'); INSERT INTO CLASS VALUES ('10022', 'QM-261', '2', 'TTh 1:00-2:15 p.m.', 'KLR200', '114'); INSERT INTO CLASS VALUES ('10023', 'QM-362', '1', 'MWF 11:00-11:50 a.m. ' 'KLR200', '162'); INSERT INTO CLASS VALUES ('10024', 'QM-362', '2','TTh 2:30-3:45 p.m.', 'KLR200', '162'); "

Step by Step Solution

There are 3 Steps involved in it

Step: 1

Show all student number first name last name and hire date concatenated in one column SELECT stunum ... blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Concepts of Database Management

Authors: Philip J. Pratt, Mary Z. Last

8th edition

1285427106, 978-1285427102

More Books

Students also viewed these Algorithms questions

Question

Describe the purpose of the SELECT command in relational algebra.

Answered: 1 week ago