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.