70-461

Please fill in the optional word: Select * from tblFirst LEFT ____ JOIN tblSecond ON ...

"outer"

tblemployee previously existed and contains two columns: employeenumber and firstname. alter tblemployee and make employeenumber the primary key. then insert some data.

alter table tblemployee add constraint myprime primary key (employeenumber) insert into tblemployee values (3, 'Anna')

in tblemployee add a check that removes the period from middle names. oooh, I forgot to mention that your table currently contains middle names with periods.

alter table tblemployee with nocheck add constraint noperiodshere check ((replace(middlename,'.','') = middlename) or (middlename is null))

in tbldepartment there is a column called departmenthead that holds 20 instead of 30 characters. correct this. also, this entry is optional

alter table tbldepartment alter column departmenthead varchar(30) null

in tblemployee add a check to dob that says people's birthdays must have occurred from 1900-01-01 to now.

alter table tblemployee add constraint dobcheck check (dob between '1900-01-01' and getdate())

add a rule to tbltransaction that says that there cannot be duplicate entries with the same amount, dateoftransaction, and employeenumber

alter table tbltransaction add constraint unqtransaction unique(amount, dateoftransaction,employeenumber)

join 'my salary is ' to '2345.6'

select 'my salary is ' + convert(varchar(20),2345.6)

join 'my salary is ' to '2345.6', and include appropriate montary indicator

select 'my salary is ' + format(2345.6, 'C', 'en-US')

declare @myvar NVARCHAR(20) = N'hello' how many bytes are used in N'hello'

1 byte for each letter of hello, then multiply by 2 for the 'N', and add two bytes becuase it is a VAR. answer is 12

complete the following statement: select tblemployee.employeenumber, firstname, lastname, sum(amount) as mysum from tblemployee join tblTransaction on tblemployee.employeenumber = tblTransaction.employeenumber

SAFJOGB: add the following to the end of the script: "group by tblemployee.employeenumber , firstname, lastname". you have to add the "group by" statement because of the inclusion of the sum(amount) in the select statement

add the column dateofentry to the table tbltransaction along with a constraint that sets the date to the current date if none is provided

alter table tbltransaction add dateofentry datetime alter table tbltransaction add constraint defaultentrydate default getdate() for dateofentry

get rid of the column dateofentry from tbltransaction

alter table tbltransaction drop column dateofentry

get rid of the constraint dateofentry from tbltransaction

alter table tbltransaction drop constraint defaultentrydate

"add a rule to tbltransaction that says that there cannot be duplicate entries with the same amount, dateoftransaction, and employeenumber. get rid of the constraint defined previously

alter table tbltransaction drop constraint unqtransaction

provide an example of how to update (including delete) all keys associated with a foreign key. then provide an example of deleting an entry.

alter table tbltransaction with nocheck add constraint myforeigncheck foreign key (employeenumber) references tblemployee(employeenumber) on update cascade on delete cascade delete tblemployee where employeenumber = 123

make a constraint that attaches the key in one table to a primary key in one another and then automatically updates all associated keys. then provide an example of updating an entry.

alter table tbltransaction with nocheck add constraint myforeigncheck foreign key (employeenumber) references tblemployee(employeenumber) on update cascade update tblemployee set employeenumber = 9123 where employeenumber = 123

a colleague needs access to the following info for employees with an employee number 120 to 139: department (from tbldepartmet), employeenumber (from tblemployee), and sum of the amount (from tbltransaction).

begin transaction go create view [dbo].[viewByDepartment] as select D.department, T.employeenumber, sum(T.amount) as TotalAmount from tbldepartmente as D left join tblemployee as E on D.department = E.department left join tblTransaction as T on E.employeenumber = T.employeenumber where T.employeenumber between 120 and 139 group by D.department, T.employeenumber go rollback transaction

a colleague needs access to the following info for employees with an employee number 120 to 139: department (from tbldepartmet), employeenumber (from tblemployee), and sum of the amount (from tbltransaction). This colleague will want the ability to insert new data, and you want to make sure your colleague does not remove old data. What do you do?

begin transaction go create view [dbo].[viewByDepartment] as select D.department, T.employeenumber, sum(T.amount) as TotalAmount from tbldepartmente as D left join tblemployee as E on D.department = E.department left join tblTransaction as T on E.employeenumber = T.employeenumber where T.employeenumber between 120 and 139 group by D.department, T.employeenumber with check option go rollback transaction

for everyone with an employeenumber between 3 and 10, replace their employeenumber with the value 194. display the changed employeenumber. if there is not a begin and rollback it's wrong!

begin transaction! update tbltransaction set employeenumber = 194 output inserted.employeenumber,deleted.employeenumber where employeenumber between 3 and 10 rollback transaction

create tbl3 such that it that automatically generates only odd values for employeenumber and sorts by employeenumbers (starting from number 15). there should also be a firstname column. Then insert some data. then create an entry that sets the employeenumber to 40. then get the value for the last identity used in tblemployee

create table tbl3 (employeenumber int constraint myconstraint primary key identity(15,2), firstname nvarchar(20)) insert into tbl3 values ('Anna') set identity_insert tbl3 on insert into tbl3(employeenumber, firstname) values (40,'Anna') select ident_current('dbo.tbl3')

create a table (name it tblFirst) with two columns (name them myCol and mySecondCol). place 678 and 890 into that table. show the values in your first column.delete the data in the table. delete the table

create table tblFirst (myCol int, mySecondCol, int) insert into tblFirst values (678),(890) select myCol from tblFirst delete from tbFirst drop table tblFirst

make a table, tbltransaction, that says that there cannot be duplicate entries with the same amount smallmoney not null, dateoftransaction smalldateandtime not null, and employeenumber int not null. after you've done that, remove the constraint.

create table tbltransaction (amount smallmoney not null, dateoftransaction smalldatetime not null, employeenumber varchar(20) not null, constraint unqtransaction (amount, dateoftransaction,employeenumber)) it is not possible to remove a constraint that was made at table creation. instead, you 'drop table tbltransaction'

create a variable for 'hello'. then select the two leftmost letters and put that in one column. Select the two rightmost letters and put them in another column. select the two letters in the middle and put them in a third column

declare @chrascii as nvarchar(10) set @chrascii = N'hello' select left(@chrascii,2) as myleft, right(@chascii,2) as myright, substring(@myascii,2,3) as middleletters

store hello. print it out, along with it's length and datalength

declare @mychar as nvarchar(10)! set @mychar = N'hello' select @mychar as colOne, len(@mychar) as myLength, datalength(@mychar) as dataLength

create a temp value of 3 billion and show the value. use smallest data type necessary

declare @myvar as bigint set @myvar = 3 billion select @myvar as myVariable

create a temp value for 5.66

declare @myvar as decimal(3,2) set @myvar = 5.66

create a temp value of -2,147,483,648 and show the value. use smallest data type necessary

declare @myvar as int set @myvar = -2,147,483,648 select @myvar as myVariable

create a variable as ' hello '. then trim out the spaces

declare @myvar as nvarchar(10) set @myvar = ' hello ' select ltrim(rtrim(@myvar)) as trimmed

create a variable as 'hello'. then replace the 'l's with 'L's

declare @myvar as nvarchar(10) set @myvar = 'hello' select replace(@myvar,'l','L') as replaced

create a variable as 'hello'. then make it all uppercase, then make it all lowercase

declare @myvar as nvarchar(10) set @myvar = 'hello' select upper(@myvar) as upperCol, lower(@myvar) as lowerCol

create a temp value of -32768 and show the value. use smallest data type necessary

declare @myvar as smallint set @myvar = -32768 select @myvar as myVariable!

create a temp value of 255 and show the value. use smallest data type necessary

declare @myvar as tinyint set @myvar = 255 select @myvar as myVariable

using the follow script, get rid of all the employee numbers with a value of null: select E.employeenumber as Enumber, E.firstname, E.lastname, T.employeenumber as Tnumber, sum(T.amount) as tamount from tblemployee as E left join tbltransaction as T on E.employeenumber = T.employeenumber where T.employeenumber is null group by E.employeenumber, E.firstname, E.lastname, T.employeenumber order by E.employeenumber, T.employeenumber, E.firstname, E.lastname

delete tblTransaction from tblTransactoin where employeenumber in (select Tnumber from ( select E.employeenumber as Enumber, E.firstname, E.lastname, T.employeenumber as Tnumber, sum(T.amount) as tamount from tblemployee as E left join tbltransaction as T on E.employeenumber = T.employeenumber group by E.employeenumber, T.employeenumber, E.firstname, E.lastname, ) as newTable where Tnumber is null) select count(*) from tblTransaction

Using: ""select system_type_id, column_id, system_type_id / column_id as Calculation from sys.all_columns", Please round the fractions up (so 4.153 would round up to 5).

from sys.all_columns select system_type_id, column_id, ceiling(system_type_id * 1.000/ column_id) as Calculation

Using: "select system_type_id, column_id, system_type_id / column_id as Calculation from sys.all_columns", Please round the fractions in the Calculation column down to the next whole number (so 4.153 would round down to 4)

from sys.all_columns select system_type_id, column_id, floor(system_type_id * 1.000/ column_id) as Calculation

Using: ""select system_type_id, column_id, system_type_id / column_id as Calculation from sys.all_columns", Please round the fractions to the nearest one decimal place (so 4.153 would round up to 4.2).

from sys.all_columns select system_type_id, column_id, round(system_type_id * 1.000/ column_id,1) as Calculation

Using: ""select system_type_id, column_id, system_type_id / column_id as Calculation from sys.all_columns", Multiply the first field, system_type_id, by 2, and then convert it to a tiniyint. If it doesn't work, convert it using a function which gives a NULL if it doesn't work properly

from sys.all_columns select system_type_id, column_id, try_convert(tinyint, system_type_id * 2)/ column_id,1 as Calculation select system_type_id, column_id, system_type_id * 1.000 / column_id as Calculation

You want to create or alter a view (not part of this question). You're not sure if the view already exists. What do you do?

if exists (select * from information_schema.views where [table_name] = 'viewByDepartment' and [table_schema] = 'dbo') *****indent*****drop view dbo.viewByDepartment

add (132,'2015-07-07',999.99) to viewByDepartment

insert into viewByDepartment(employeenumber, dateofTransaction,TotalAmount)

what other arguments are acceptable for "on update"?

on update set null on update set default

rewrite the following script as a derived table select E.employeenumber as Enumber, E.firstname, E.lastname, T.employeenumber as Tnumber, sum(T.amount) as tamount from tblemployee as E left join tbltransaction as T on E.employeenumber = T.employeenumber where T.employeenumber is null group by E.employeenumber, E.firstname, E.lastname, T.employeenumber order by E.employeenumber, T.employeenumber, E.firstname, E.lastname

select * from ( select E.employeenumber as Enumber, E.firstname, E.lastname, T.employeenumber as Tnumber, sum(T.amount) as tamount from tblemployee as E left join tbltransaction as T on E.employeenumber = T.employeenumber group by E.employeenumber, E.firstname, E.lastname, T.employeenumber ) as newTable where Tnumber is null order by Enumber, Tnumber, firstname, lastname

check what views are currently in existance

select * from sys.views

rewrite the following script, but use aliases. select departmenthead, sum(amount) as sumofamounts from tbldepartmente left join tblemployee on tbldepartmente.department = tblemployee.department left join tblTransaction on tblemployee.employeenumber = tblTransaction.employeenumber group by departmenthead order by departmenthead

select D.departmenthead, sum(T.amount) as sumofamounts! from tbldepartmente as D left join tblemployee as E on D.department = E.department left join tblTransaction as T on E.employeenumber = T.employeenumber group by D.departmenthead order by D.departmenthead

select [name] from sys.all_columns 1. Add the letter A to the end of each name. "2. Add the letter Ⱥ to the end of each name. (You may want to copy and paste this letter, as it is an A with a stroke through it). If you are getting question marks, then give it another try." 3. Remove the first character from name. 4. Remove the last original character from name

select [name] + 'A' select [name] + N 'Ⱥ' select substring([name], 2, len(name)) + N'Ⱥ' select substring([name], 1, len(name) - 1)+ N'Ⱥ'

declare @fn as nvarchar(20) declare @mn as nvarchar(20) declare @ln as nvarchar(20) set @fn = 'Anna' set @ln = 'Jimenez' using the script above, how would you produce a full name in a single column?

select concat(@fn, + ' ' + @mn, + ' ' + @ln) as fullName

the following script produces a column of departments and column of counts in each department. create a derived table "aka, what is the script" that produces only the column of departments. select department, count(*) as departmentcount from tblemployee group by department

select department from (select department, count(*) as departmentcount from tblemployee group by department) as newdepartmenttable

using the department column of the tblemployee table, write a query that returns the different types of entries in that column

select distinct department from tblemployee

create a new table from the results of the following: select distinct department, '' as departmenthead from tblemployee

select distinct department, '' as departmenthead into tbldepartment from tblemployee

you have 3 tables: tblemployee contains empnumber, firstname, middlename, lastname, empid, dob and department. tbltransaction contains amount, dateoftransaction and empnumber. tbldept contains department and departmenthead. write a script that joins the three tables and answers the question, for each department, what is the total amount of transactions? The script should also order the results and then insert in a new row, Accounts, James

select tbldepartmente.department, sum(Amount) as sumofAmount from tbldepartmente left join tblemployee on tbldepartmente.department = tblemployee.department left join tbltransaction on tblemployee.employeenumber = tbltransaction.employeenumber group by tbldepartmente.department order by department insert into tbldepartmente values ('Customer Relations', 'Andrew')

say you have a table with a column of lastnames (lastname) and a column of birthdays (dob). do the following only for people born after 1986-01-01: while making a representative column name for each column displayed, display the top 10 results for number of people who's lastname starts with the letter a, the letter b, c, etc.. display the results only for letters that have a start count of at least 50 (this is redundant, i know, but do it for the sake of the excerise), along with the letter. sort the entries from greatest number of people per letter to smallest number of people per letter

select top(10) left(lastname,1) as initial, count(*) as diffinitial from tblemployee where dob > '1986-01-01' group by left(lastname,1) having count(*)>=50 order by count(*) desc

given a column dob, display a column for the year!

select year(dob) as yeardob, count(*) as numberborn

make an acronym for Select, From, Where, Group By, Having, Order by. With which of these six keywords can you use used aliases named in the Select clause?

small felines wear gold bracelets and howl over breakfast. order by

what is the sql version of undo ?

start your script with "begin transaction" and end your script with "rollback transaction"

you took department from tbldepartment, employeenumber from tbltransaction, dateofTransaction from tbltransaction, and amount from tbltransaction. add ('Customer Relations', '132,'2015-07-07',999.99) to viewByDepartment

trick question, you CAN add values to a single base table per view at a time, but you CANNOT add values to multiple base tables in a view at the same time.

change employeenumber 132 to 142 in your viewByDepartment view

update viewByDepartment set employeenumber = 142 where employeenumber = 132

Will these two SELECT statements evaluate the same? Select * from tblFirst LEFT JOIN tblSecond ON ... Select * from tblSecond RIGHT JOIN tblFirst ON ...

yes! the names of the tables is confusing, so pretend tblFirst is named Apple and pretend tblSecond is named Banana. Remember also that "Left" means "left of the word 'Join' in this select statement". Remember that "Right" means "right of the word join in this select statement". Then the first statement reads: select everything from the apple table and put it in our new table. select whatever is in the banana table that has an entry in the apple table and put it in our new table. Then the second statement reads: select eerything from the apple table and put it in our new table. select whatever is in the banana table that has an entry in the apple table and put it in our new table.

What is the outcome of the following query: select month(dob) as monthdob, count(*) as countbymonth from tblemployee group by month(dob) asc

you get an error. 'asc' goes with 'order by'

What is the outcome of the following query: select month(dob) as monthdob, count(*) as countbymonth from tblemployee

you get an error. you need a 'group by' statement for this to work.