Answered step by step
Verified Expert Solution
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.
- Show all student number first name, last name, hire date all concatenated in one column.
- Show the student date of birth (stu_dob), the birth year, month and day in separate columns.
- Show the students attributes, and the dob according to format code 102. Format code 102 show the date as follows yyyy.mm.dd
- Show all students born between 1970 and 1975. Use the between operator. Show their first and last name in capital letters.
- Show the first 3 characters of the course description.
- Show the students that do not have initials.
- 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*
/* 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 ...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started