/* Lab 7
   Philip Bock */

use Pubs;

-- Question 1

select DATENAME(dw, 'Dec 25, 2010');

-- Question 2

select DATEDIFF(d, GETDATE(), 'Dec 25, 2010');

-- Question 3

select DATEDIFF(d, GETDATE(), 'Dec 25, ' + CONVERT(char, DATEPART(yy, GETDATE())));

-- Question 4

select fname + ' ' + lname 'Name', pub_name 'Employer', job_desc 'Job Description' from employee
	join publishers on (employee.pub_id = publishers.pub_id)
		join jobs on (employee.job_id = jobs.job_id)
where DATENAME(dw, hire_date) in ('Saturday', 'Sunday');

-- Question 5

select pub_name, title, round(ytd_sales * price, 1) 'Gross Sales' from titles
	join publishers on titles.pub_id = publishers.pub_id
where round(ytd_sales * price, 1) >= 100000;

-- Question 6

select pub_name from publishers where pub_id in
	(select pub_id from titles where title_id in
		(select title_id from sales where DATEDIFF(yy, ord_date, GETDATE()) < 8));

-- Question 7

select au_fname + ' ' + au_lname 'Name', ASCII(UPPER(SUBSTRING(au_fname, 1, 1))) 'Initial'
from authors where au_id in
	(select au_id from titleauthor where title_id in
		(select title_id from titles where DATEDIFF(yy, pubdate, GETDATE()) <= 10));

-- Question 8

select hire_date, DATEADD(d, 15, hire_date), lname from employee;

-- Question 9

select DAY(GETDATE()) 'Day', DATENAME(mm, GETDATE()) 'Month', YEAR(GETDATE()) 'Year'

-- Question 10

select au_lname 'Name', LEN(au_lname) 'Length' from authors;

-- Question 11

select UPPER(LEFT(au_fname, 1)) + SUBSTRING(au_fname, 2, 4) from authors;

-- Question 12

use tempdb;

drop table TestValues;
create table TestValues (coll real not null);

insert into TestValues values (-10);
insert into TestValues values (10);
insert into TestValues values (3.66);
insert into TestValues values (-3.66);
insert into TestValues values (2.22);
insert into TestValues values (-2.22);

select coll 'Test Value', CEILING(coll) 'CEILING', FLOOR(coll) 'FLOOR',
ROUND(coll, 2) 'ROUND', EXP(coll) 'EXP', SIGN(coll) 'SIGN', SQUARE(coll) 'SQUARE',
LOG(ABS(coll)) 'LOG', LOG10(ABS(coll)) 'LOG10', SQRT(ABS(coll)) 'SQRT'
from TestValues;
