1. Good Afternoon

2. By default the size of a database is 1 MB • A database consists of • Master Data File (.mdf) • Primary Log File (.ldf) Creating a database

3. Creating a database Syntax: CREATE DATABASE <dbname> Ex: CREATE DATABASE ITM • Changing a database Syntax: USE<dbname> Ex: USE ITM • Dropping a database Syntax: DROP DATABASE <dbname> Ex: DROP DATABASE ITM Database operations

4. int : Stores whole number float : Stores real numbers char/varchar : Stores characters smalldatetime : Stores date and time SQL Server Data types

5. Arithmetic : +, -, * , /, % Assignment : = Comparison : >, <, <=, >=, <>, =, !=, Logical :AND, OR, NOT, IN, LIKE, BETWEEN, ANY, ALL,EXISTS, SOME String : + (Concatenation) Operators

6. Creating a Table Syntax: Create table <tablename> ( col1 data type (size), col2 data type (size) ) Ex: Create table student ( Roll char (10), Name varchar(30) ) Creates a table with two columns Table management

7. Deleteing a table Syntax: Drop table <tablename> Ex: Drop table ITM - Deletes the table structure

8. Used to modify table structure • Add new column • Change data type of existing column • Delete a column • Add or remove constraints like foreign key, primary key Alter statements

9. Syntax: 1) ALTER TABLE <TABLENAME> ADD <COLUMN NAME DATATYPE (SIZE) 2) ALTER TABLE <TABLENAME> ALTER COLUMN <COLUMNNAME DATATYPE (SIZE) 3) ALTER TABLE <TABLENAME> DROP COLUMN <COLUMN NAME> Ex: 1) ALTER TABLE ITM ADD DOB SMALLDATETIME 2) ALTER TABLE ITM ALTER COLUMN NAME VARCHAR(40) 3) ALTER TABLE ITM DROP COLUMN DOB

10. ????????

11. Thank You

12. Truncate table tablename • Removes all rows in a table • Resets the table. • Truncate does the following, where as delete statement does not • Releases the memory used • Resets the identity value • Does not invoke delete trigger Truncate statement

13. To execute a statement in MS SQL, Select the statement and Click on the Execute button in the query analyser or press F5 • This is used to retrive records from a table • Eg. Select * from table1; • This will fetch all rows and all columns from table1 • Eg. Select col1,col2 from table1 • This will fetch col1 and col2 from table1 for all rows • Eg. Select * from table1 where <<condn>> • This will fetch all rows from table1 that satisfies a condition • Eg. Select col1,col2 from table1 where <<condn>> • This will fetch col1 and col2 of rows from table1 that satisfies a condition Select Statements

14. Aggregate functions • Sum(col1): sum of data in the column col1 • Max(col1): data with maximum value in col1 • Min(col1): data with minimum value in col1 • Avg(col1): Average of data in col1 • Count(col1): Number of not null records in table • Grouping – Group by col1 : Groups data by col1 • Ordering – Order by col1 : Orders the result in ascending order (default order) of col1 • Filtering – Where <<condn>> and Having <<condn>> Select Options

15. Inserting data to all columns • Insert into tablename(col1,col2) values(v1,v2) • Insert into tablename values(v1,v2) • Inserting data to selected columns • Insert into tablename(col1) values (v1) • Insert into tablename(col2) values (v2) Insert statements

16. Update table tablename Set colname=value - This updates all rows with colname set to value Update table tablename Set colname=value Where <<condition>> - This updates selected rows with colname as value only if the row satisfies the condition Update statement

17. Delete from table1; Deletes all rows in table1 Delete from table1 where <<condition>> Deletes few rows from table1 if they satisfy the condition Delete statements

18. Viewing tables in a data base: • Exec sp_tables “a%” • This gives all tables in the current database that starts with “a” • Viewing table strucure: • Exec sp_columns <<tablename>> • Exec sp_columns student; More table commands

19. Cross Join • Cartesian product. Simply merges two tables. • Inner Join • Cross join with a condition. Used to find matching records in the two tables • Outer Join • Used to find un matched rows in the two tables • Self Join • Joining a table with itself Joins

20. There are two tables A and B A has a column Id and data (1,2,3) B has a column Id and data (A,B) If I put Select A.Id, B.Id from A,B This generates output as A 1 B 1 C 1 A 2 B 2 C 2 Cross Join

21. There is a table called Emp with the following structure: empidenamemgrid 1 A null 2 B 1 3 C 1 4 D 2 If I want to print all managers using self join, I should write quey as: select e1.ename from emp e1,emp e2 where e1.mgrid = e2.empid Self Join

22. I have 2 tables Student(sid,Name) and Marks(Sid,Subject,Score) If I want to print the marks of all students in the following format, Name Subject Score Select Name,Subject,Score from Student s join Marks m On s.sid = m.sid Inner Join

23. Right outer Join • Print all the records in the second table with null values for missing records in the first table • Left outer Join • Print all the records in the first table with null values for missing records in the second table • Full outer Join • Prints all records in both the table with null values for missing records in both the table Outer Join

24. I have a table Employee (Eid, Ename, Mid) and a table Machine (Mid,ManufacturerName) Employee Eid EName Mid 1 ABC 1 2 DEF 3 Machine Mid ManufacturerName 1 Zenith 2 HP Left Outer Join

25. I want to print the employee name and machine name. If I write a query using inner join, then the second employee will not be displayed as the mid in his record is not avilable with the second table. So I go for left outer join. The query is as shown below: Select Ename, ManufacturerName from Employee e left outer join Machine m on e.Mid = m.Mid Left Outer Join

26. Assume data in the tables like this: Employee Eid EName Mid 1 ABC 1 2 DEF Machine Mid ManufacturerName 1 Zenith 2 HP Right outer Join

27. If I want to find which machine is unallocated, I can use right outer join. The query is as follows: Select Ename, ManufacturerName from Employee e right outer join Machine m on e.Mid = m.Mid This yields a result ABC Zenith HP Right Outer Join

28. Assume data in the tables like this: Employee Eid EName Mid 1 ABC 1 2 DEF 3 GHI 2 Machine Mid ManufacturerName 1 Zenith 2 HP 3 Compaq Full Outer Join

29. If I want to find people who have been un allocated with a system and machines that are been un allocated, I can go for full outer join. Query is like this: Select Ename, ManufacturerName from Employee e full outer join Machine m on e.Mid = m.Mid This yields a result ABC Zenith DEF GHI HP Compaq Full Outer Join

30. Views are logical tables They are pre compiled objects We can select few columns or rows from a table and put the data set in a view and can use view in the same way as we use tables Views

31. Create views: Create view viewname as select stmt Create view view_emp as select empid, empname from employee; • Select from views: Select * from viewname Select empid,empname view_emp; • Drop views: Drop view viewname Drop view view_emp; Views

32. Substring(string,start,length) – Will fetch characters starting at a specific index extending to length specified. Left(string,length) – Fetches number of characters specified by length from left of the string Right(string,length) – Fetches number of characters specified by length from right of the string Len(string) – Returns the length of a string String Functions

33. Ltrim(string) – Removes leading spaces in a string Rtrim(string) – Removes trailing spaces in a string Lower(string) – Converts the characters in a string to lower case Upper(string) – Converts the characters in a string to upper case String Functions

34. ABS(Number) – Fetches the modulo value (Positive value) of a number CEILING(Number) – Fetches the closest integer greater than the number FLOOR(Number) – Fetches the closest integer smaller than the number EXP(Number) – Fetches the exponent of a number Numeric Functions

35. POWER(x,y) – Fetches x raised to the power of y LOG(Number) – Fetches the natural logarithmic value of the number LOG10(Number) – Fetches log to the base 10 of a number SQRT(Number) – Fetches the square root of a number Numeric Functions

36. Indexes make search and retrieve fast in a database • This is for optimizing the select statement • Types of index • Unique • Non unique • Clustered • Non clustered Indexes

37. Create index indexname on tablename(columnname) This creates a non clustered index on a table Create unique clustered index index_name on Student(sname); This creates a unique and clustered index on the Column Sname. Index

38. This creates an auto increment for a column If a table has a column with sequence or auto increment, the user need not insert data explicitly for the column Sequence is implemented using the concept of Identity Sequences

39. Identity has • A seed • An increment • Seed is the initial value • Increment is the value by which we need to skip to fetch the nextvalue • Identity(1,2) will generate sequence numbers 1,3,5,7… Identity

40. Create table table1 ( Id integer identity(1,1), Name varchar(10) ) It is enough if we insert like this: Insert into table1(name) values(‘Ram’); Ram will automatically assigned value 1 for id Sample