question

Hi, can someone help check my syntax if they are correct. Thx --1) What does the acrocyn T-SQL stands for? Transact-Structured Query Language --2) What keyword in a SQL query do you use to extract data from a database? SELECT --3) What keyword in a SQL query do you use to modify data from a database table? UPDATE --4) What keyword in a SQL query do you use to add data from a database table? INSERT --5)What is the difference between the following Joins? a) Left Join performs a join starting with the first(leftmost) table and then any matching second (right-most) table records. b) Inner Join returns rows when there is a match in both tables c) Right Join performs a join starting with the second (right most) table amd then any matching first(left-most) table records. --6)What is the differnce between a table and a view A table is a physical object in the database while a view is a logical object in the database. A table contains data, a view is just a SELECT statement which has been saved in the database (more or less, depending on your database). The advantage of a view is that it can join data from several tables thus creating a new view of it. --7) What is the difference between a temporary and variable table Both Temporary Tables (a.k.a #Tables) and Table Variables (a.k.a @Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing. 1)Temp tables are created in tempdb. They act like regular table in that you can query their data via SELECT and modify their data via UPDATE, INSERT and DELETE statements. Table variable is created in memory and cannot be modified nor can we explicitly drop it. 2)Temp tables honor the explicit transactions defined by the user. Table variable does not participate in the explicit transactions defined by the user. 3)Temp tables are not allowed in User Defined Functions. Table variables can be used in UDF. 4)Temp tables support adding indexes explicitly after Temp table creation and it can also have the implicit indexes. Table variable do not allow explicit creation of indexs after its declaration, the only way implicit indexes are created is during declaration of table. 5)Temp tables records transaction logs while Table variables transactions are not logged. 6)Stored procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy. 7)Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement. --8) Create two tables and answer the questions below USE [TestDemo] DROP TABLE TABLEA CREATE TABLE TABLEA ( Field1 int ) INSERT INTO TABLEA (Field1) VALUES (1), (2), (3), (4), (4), (5), (6) DROP TABLE TABLEB CREATE TABLE TABLEB ( Field1 int ) INSERT INTO TABLEB(Field1) VALUES (2), (5), (7), (6), (3), (3), (9) --8) Display data from TableA where the values are identical in TableB SELECT a.* FROM [dbo].[TABLEA] a INNER JOIN [dbo].[TABLEB] b ON a.Field1 = b.Field1 select * from TABLEA select * from TABLEB --9) Display data from TableA where the values are not available in TableB SELECT a.* FROM [dbo].[TABLEB] b RIGHT OUTER JOIN [dbo].[TABLEA] a ON a.Field1 = b.Field1 --10) Display data from TableB where the values are not in TableA SELECT b.* FROM [dbo].[TABLEB] b RIGHT OUTER JOIN [dbo].[TABLEA] a ON a.Field1 = b.Field1 select * from TABLEA select * from TABLEB --11) Display Unique values from TableA SELECT DISTINCT * FROM [dbo].[TABLEA] --12) Display the total number of records, per unique value in TableA SELECT DISTINCT COUNT(*) AS DistinctCount FROM [dbo].[TABLEA] --13) Display the unique value from TableB where it occurs more than once SELECT COUNT(*) AS NoOfCount, [Field1] FROM [dbo].[TABLEB] GROUP BY [Field1] HAVING COUNT(*) > 1 --14) Display the greatest value from TableB SELECT MAX([Field1]) AS LargestValue FROM [dbo].[TABLEB] GO --15) Display the small value from tablA SELECT MIN([Field1]) AS SmallestValue FROM [dbo].[TABLEA] select * from TABLEA select * from TABLEB --16) Write a SQl statement to create a variable called Variable1 that can handle the value such as "Welcome to planet earth" DECLARE @Variable1 char(50) SET @Variable1 = 'Welcome to planet earth' SELECT @Variable1 AS Message --17) Write a SQl statement that constructs a table called Table1 with the following fields; a) Field1 --this field stores number such as 1,2,3 etc b) Field2 --this field stores the date and time c) Field3 - this field stores the text up to 500 characters CREATE TABLE Table1 ( Field1 int, Field2 datetime, Field3 varchar(500) ) --18)Write a SQL statement that adds the following records to Table1; INSERT INTO Table1(Field1, Field2, Field3) VALUES (34, '1/19/2012 08:00AM', 'Mars Saturn'), (65, '02/15/2012 10:30am', 'Big Bright Sun'), (89, '3/31/2012', 'Red Hot Mercury') select * from Table1 --19) Write a SQL statement to change the value for Field3 in Table1 to the value stored in Variable1(From question 16), on the record that is 34 DECLARE @Variable1 char(50) SET @Variable1 = ( SELECT CAST([Field3] AS varchar(500)) FROM [dbo].[Table1] WHERE [Field1] = 34 ) SELECT @Variable1 AS Message --20) Write a SQL statement for record 89 to return the total number of character for field3 SELECT LEN([Field3]) AS Characterlenght FROM [dbo].[Table1] WHERE [Field1] = 89 --21) Write a SQL statement for record 65 to return the first occurence of a space in Field3 SELECT CHARINDEX(' ', [Field3]) FROM [dbo].[Table1] WHERE [Field1] = 65 --22) Write a SQl statement for record 65 to return the value "Bright" from field3 SELECT SUBSTRING('Big Bright Sun', 5, 6) FROM [dbo].[Table1] WHERE [Field1] = 65 --23) Write a SQL statement for record 34 to return the day from the Field2 SELECT DATEPART(DD, [Field2]) FROM [dbo].[Table1] WHERE [Field1] = 34 --24) Write a SQL statement for record 34 to return the first day of the month from field2 SELECT DATEADD(MM, DATEDIFF(MM,0, [Field2]), 0) FROM [dbo].[Table1] WHERE [Field1] = 34 --25) Write a SQL statement for record 34 to return the previous end of the month from field2 SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,[Field2]),0)) FROM [dbo].[Table1] WHERE [Field1] = 34 --26) Write a SQL statement for record 34 to return the day of the week from the field2 SELECT DATENAME(DW, [Field2]) AS WEEKDAY FROM [dbo].[Table1] WHERE [Field1] = 34 --27) Write a SQL statement for record 34 to return the date as CCYYMMDD from field2 SELECT CONVERT(char(8), [Field2], 112) FROM [dbo].[Table1] WHERE [Field1] = 34 --28) Write a SQL statement to add a new column, Field 4(datatype can be of any preference) to Table1 ALTER TABLE [dbo].[Table1] ADD Field4 varchar(50) --29) Write SQl statement to remove record 65 from Table1 DELETE [dbo].[Table1] WHERE [Field1] = 65 SELECT * FROM Table1 --30)Write a SQL statement to wipe all records in Table1 DELETE [dbo].[Table1] ---)Write a SQL statement to rem