1 / 40

Good Afternoon

Good Afternoon. 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. Creating a database Syntax: CREATE DATABASE < dbname > Ex: CREATE DATABASE ITM Changing a database Syntax: USE< dbname >

ursula
Download Presentation

Good Afternoon

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related