I have some SQL Challenge, Can any of you answer the following questions ?
Select the most accurate answer to the question below. Assume a SQL SERVER 2017 database engine QUESTION 1 select * from eso_data.dbo.SPECIALS where com_nme like 'angliss%' order by version; What does the above SQL statement do A. It does not work B. It selects all the data from the table specials which is equal to angliss C. It selects all the data which is like angliss from the table specials D. It selects all the data which is like angliss from the table special E. It selects all the data where com_nme field is like angliss from the table specials QUESTION 2 insert into CAD.DEF_UNIT select * from dbo.DEF_UNIT_RESCUE where unityp = 'RESCUE'; A. Use of the wildcard is not allow in sql server so it does nothing B. Inserts all new records into DEF_UNIT from DEF_UNIT_RESCUE C. Inserts all records where unityp = 'RESCUE' into DEF_UNIT_RESCUE D. Inserts all records where unityp = 'RESCUE' into DEF_UNIT E. Selects all the records from DEF_UNIT_RESCUE where unityp = 'RESCUE' QUESTION 3 Select the best statement to update a record Schema = CAD Table name = DEF_UNIT Primary Key = UNID Fields = UNID,UNITYP,X_CORD,Y_CORD,CFA_BRIG_CAT,STOS_METHOD Requirement is a new value for UNITYP of RESCUE where the UNID is equal to DANDR A. update DBO.DEF_UNIT_RESCUE SET UNITYP = 'RESCUE' WHERE UNID = 'R3' and UNITYP = ( SELECT UNITYP FROM CAD.DEF_UNIT WHERE UNITYP = 'RESCUE'); B. UPDATE DEF_UNIT SET UNITYP = 'RESCUE' WHERE UNID = 'DANDR'; C. UPDATE CAD.DEF_UNIT SET UNITYP = 'RESCUE' WHERE UNID = 'DANDR'; D. UPDATE DBO.DEF_UNIT SET UNITYP = 'RESCUE' WHERE UNID = 'DANDR'; E. None of the above requirements will meet what being ask for QUESTION 4 Schema = DBO Table name = PAGE_PAGERS Fields = RECIPIENT_NAME, SERVICE_PROVIDER,PGR_NUMBER,SEQ_ORDER Requirements is to delete all the records where service_provider = ORANGE A. DELETE FROM DBO.PAGE_PAGERS WHERE SERVICE_PROVIDER = 'ORANGE'; B. DELETE FROM DBO.PAGE_PAGERS WHERE SERVICE_PROVIDER like 'ORANGE%'; C. DELETE FROM DBO.PAGER_PAGE WHERE SERVICE_PROVIDER = 'ORANGE'; D. DELETE FROM DBO_PAGER_PAGER WHERE SERVICE_PRVIDER = 'ORANGE'; E. None of the above their all incorrect QUESTION 5 Default Schema is already set (not required) Table name = DEF_UNIT_RESCUE Fields = UNID,UNITYP,X_CORD,Y_CORD,CFA_BRIG_CAT,STOS_METHOD Requirement is to remove all records from the table DEF_UNIT_RESCUE A. DELETE * FROM DEF_UNIT_RESCUE B. TRUNCATE TABLE DEF_UNIT C. DROP TABLE DEF_UNIT_RESCUE D. DELETE FROM DEF_UNIT_RESCUE E. None of the above all incorrect QUESTION 6 What does the following script do USE [LIVECADmap1] GO /****** Object: Table [DW].[EVENT_DATA] Script Date: 06/09/2009 09:51:01 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [DW].[EVENT_DATA]( [AG_ID] [varchar](9) NOT NULL, [ALARM_LEV] [decimal](10, 0) NULL, [CAN_DISPATCH] [varchar](1) NULL, [CASE_NUM_ID] [decimal](10, 0) NULL, [RESP_PLAN_NAME] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF A. It creates a temporary table call EVENT_DATA in the schema DW on database LIVECADmap1 B It creates a table call EVENT_DATA in the schema DW on database LIVECADmap1 C. It creates a table call EVENT_DATA on database DW in schema LIVECADmap1 D. It creates a table call EVENT_DATA on schema DW on database LIVECADmap E. None of the above their all incorrect QUESTION 7 What is SSIS A. Sql Server integration services B. Sql Server inspection services C. Sql Server interops services D. Sql Server Inter-xmls services E. Sql Server intergation scripts QUESTION 8 Can you use SSIS packages for reading and converting data into a sql server data from an excel spreadsheets A. No you would use DTS for this B. Yes SSIS packages are ideal for converting data from excel into sql server database table C. No you use XML for this D. You would write a K# application to do this using the .NET framework 3.5 E. SSIS is an inspection service for checking the health of the database QUESTION 9 select CARID, EQUIP, NUM_EQUIP from DEF_EQUIP where (CARID like 'R%' AND EQUIP IN ('CHAINSAW') AND NUM_EQUIP > 1); Is the above statement valid and will it return equip of CHAINSAW in a list of values from the table DEF_EQUIP A. No this is an invalid sql you cannot use > and the like in a WHERE clause B. No this is an invalid sql you cannot have two AND conditions in a SQL statement C. No this is an invalid sql you cannot have the IN CLAUSE in sql server only ORCALE supports its use D. No the brackets have to be around every statement syntax error E. Yes this statement works fine and will return equip of CHAINSAW that have CARID like R and NUM_EQUIP greater than 1 QUESTION 10 select AG_ID, ALARM_LEV, CAN_DISPATCH, CASE_NUM_ID, RESP_PLAN_NAME FROM CAD.EV_TY where (RESP_PLAN_NAME like SUBSTRING(RESP_PLAN_NAME,1,1)); Does the above sql statement work select the best answer below A. No it does not work you cannot have functions in the where clause B. Yes it works fine SUBSTRING the sql is just looking at the first char of RESP_PLAN_NAME C. Yes it works fine SUBSTRING the sql is just looking at the second char of RESP_PLAN_NAME D. No it does not work the correct function is SUBSTR , SUBSTRING is an orcale function for looking at strings E. Yes it works fine SUBSTRING the sql is just looking at the third char of RESP_PLAN_NAME QUESTION 11 select AG_ID, ALARM_LEV, CAN_DISPATCH, CASE_NUM_ID, RESP_PLAN_NAME FROM CAD.EV_TY where AG_ID IN (SELECT AG_ID FROM CAD.DEF_STATION) AND AG_ID IN (SELECT AG_ID FROM CAD.DEF_UNIT); Assume all three tables have AG_ID as a field is this a valid sql statement A. This is just incorrect sql for sql server 2005 B. This is only support in SQL SERVER 2008 not SQL SERVR 2005 C. This statement is fine D. You cannot have two select on the same field in two different tables E. Multi select are not permitted in sql server like orcale you have to use an outer join to do this QUESTION 12 Schema = CAD Table name = EVENTDATAREC Fields = AG_ID,TYPE,ENG,ALARM_LEV (note ALARM_LEV is a int) Requirement to delete all records from a table where conditions are meet AG_ID = 'POLICE' and ALARM_LEV less than or = to 3 A. DELETE * FROM EVENTDATA WHERE AG_ID = 'POLICE' AND ALARM_LEV <= 3; B DELETE * FROM CAD.EVENTDATAREC WHERE AG_ID = 'POLICE' AND ALARM_LEV <= 3; C DELETE FROM CAD.EVENTDATAREC WHERE AG_ID = 'POLICE' AND ALARM_LEV <= 3; D DELETE FROM CAD.EVENTDATAREC WHERE AG_ID = 'POLICE' AND ALARM_LEV =< 3; E All statements are incorrect QUESTION 13 INSERT INTO RESP_REQ_MAP1(ATTR_OPT, BEAT_OPT, HAS_RES,LEV3,LINE_NUMBER,QUAN,REQ_NAME, SAME_BEAT,STATION,UNID,UNITATTR,UNITYP) VALUES (0,3,0,'CGST',0,1,'CGST',0,NULL,'CCGST',0,'CGB'); Does this statement work and what is it doing A. Yes this statement works and inserts one record with values into a table call RESP_REQ_MAP1 B. No this statement does not work it only works in Orcale 10g C. Yes this statement works it updates a record with ATTR = 0 D. No this statement is missing the keyword select before VALUES E. No this statement is missing the keyword from after INSERT QUESTION 14 update CO_PL_MAP1 set spad_mslink = 10002859 where mslink = 1010329; Does this statement work and what is it doing A. Yes this statement works and updates the field mslink to equal 1010329 B. No numeric values are not support without quotes around them in sql server C. Yes this statement works and update CO_PL_MAP1 mslink field to 1010329 D. Yes this statement works and updates the Table CO_PL_MAP1 the field of spad_mslink equal to 10002859 where the mslink field - 1010329 E. No of the above would work, the sql statement is incorrect QUESTION 15 E.F.Codd the inventor of relational model introduced the concept of normalization, which of the below is correct of his concept of normalization A. Free the database of modification anomalies B. Make the database model a collection of flat files for easy user modifcation C. Allow for denomalization to increase database performace D. Free the database of modifcation anomalies by getting the design to 7th normal form E. None of the above