/* SQL Fundamentals
LOGICAL OPERATORS: AND, OR, NOT 
*/


/* Show students in the ITPROS team and with an ISYS major. */

select std_teamID, stdmajor, stdfname, stdlname
from students
where std_teamID = 'ITPROS' 
AND stdmajor = 'ISYS';

/* one way to verify output for SQL above. */

select std_teamID, stdmajor, stdfname, stdlname
from students
where std_teamID = 'ITPROS';



/* Show students who are majoring computer science (COMPSC) or 
accounting (ACCT). */

select std_teamID, stdmajor, stdfname, stdlname
from students
where stdmajor = 'COMPSC' 
OR stdmajor = 'ACCT';


/* Show students majoring in accounting OR marketing AND that 
are in the ITPROS team OR the SYSDES team.*/

/* version #1 */
select std_teamID, stdmajor, stdfname, stdlname 
from students
where stdmajor = 'ACCT' OR stdmajor = 'MKTG'
AND std_teamID = 'ITPROS' OR std_teamID = 'SYSDES';

/* is there a problem with version #1, does it correctly satisfy the question? */

/* does version #2 provide a better solution */

select std_teamID, stdmajor, stdfname, stdlname 
from students
where (stdmajor = 'ACCT' OR stdmajor = 'MKTG')
AND (std_teamID = 'ITPROS' OR std_teamID = 'SYSDES');


/* Show students who are NOT in ITPROS and NOT in SYSDES. */

select std_teamID, stdmajor, stdfname, stdlname 
from students
where  NOT std_teamID = 'ITPROS' AND NOT std_teamID = 'SYSDES';







/* IN EXACT MATCH OR BETWEEN */

/* List students whose last name starts with 'A'. */

select stdmajor, stdfname, stdlname
from students
where stdlname LIKE 'A%'; 

/* List students whose last name starts with 'A' or "D'. */

select stdmajor, stdfname, stdlname
from students
where (stdlname LIKE 'A%' or 
stdlname LIKE 'D%');

/* List students whose team ends with 'PROS'. */
select std_teamID, stdfname, stdlname
from students
where std_teamID LIKE '%PROS';

/*  List evaluation item scores between 70 and 79. */

select * 
from eval_items_scores
where score BETWEEN 70 AND 79;

/* List employees hired between 1998 and 1999. */

select BusinessEntityID, jobtitle, hiredate, salariedFlag
from AdventureWorks2008.HumanResources.Employee
where HireDate between '1998-01-01' and '1999-12-31';

/* Count how many people were hired in 1998 who work in production 
(job title starts with 'Production').  */

select count(*)
from AdventureWorks2008.HumanResources.Employee
where HireDate between '1998-01-01' and '1998-12-31'
and jobtitle like 'Production%';







/* IN, NOT IN, NULL, NOT NULL*/


/* List students who are in these teams: ITPROS, SYSDES or TECHSO.  */


select std_teamID, stdfname, stdlname
from students
where std_teamID = 'ITPROS' 
OR std_teamID = 'SYSDES' 
OR std_teamID = 'TECHSO';

select std_teamID, stdfname, stdlname
from students
where std_teamID IN ('ITPROS', 'SYSDES', 'TECHSO');


/* List students who are NOT in these teams:  ITPROS, SYSDES or TECHSO. */


select std_teamID, stdfname, stdlname
from students
where std_teamID NOT IN ('ITPROS', 'SYSDES', 'TECHSO');


/* List students who have not been assigned to a team.    */

select std_teamID, stdfname, stdlname
from students
where std_teamID IS NULL;


/* List students who are assigned to a team.    */

select std_teamID, stdfname, stdlname
from students
where std_teamID IS NOT NULL;

/* How many students are there? */

select count(*)
from students;


/* How many students have not been assigned to a team. */

select count(*)
from students
where std_teamID IS NULL;


/* How many students have been assigned to a team.  */

select count(*)
from students
where std_teamID IS NOT NULL;


/* SQL Fundamentals SORT CALCULATE */


/* List students and their team ID.  Sort by team ID. */

select std_teamID, stdmajor, stdfname, stdlname
from students
order by std_teamID;

/* List students and their team ID.  Sort by team ID and major. */

select std_teamID, stdmajor, stdfname, stdlname
from students
order by std_teamID, stdmajor;


/* Another way to identify which columns to sort by using numbers.  */

select stdfname, stdlname, stdmajor, std_teamID
from students
order by 4, 3;



/* Use column aliases column headings in output.  */

select std_teamID "Team", stdmajor "Major",
   stdfname "First Name", stdlname "Last Name"
from students
order by std_teamID, stdmajor;


/* Combine 2 columns into one (concatenate columns).  */

select std_teamID "Team", stdmajor "Major",
   stdfname + ' ' + stdlname "Student"
from students
order by 1, 2;


select std_teamID "Team", stdmajor "Major",
   stdlname + ', ' + stdfname "Student"
from students
order by 1, 2, 3;



/* Add a calculated column. */

select eval_id, eval_score_id, eval_item_id, 
   score, score * .10 "Points Earned"
from eval_items_scores
order by eval_id, eval_score_id, eval_item_id;