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*

Transcribed Image Text:

/* 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'); " /* 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'); "

Answer rating: 100% (QA)

Show all student number first name last name and hire date concatenated in one column SELECT stunum ... View the full answer