Sybase Session -2 Document

                                                                                                                                        

SYBASE SESSION -- 2 DOCUMENTS

___________________________________________________________________________________________________ Copyrights Reserved Page 1 of 10 

                                                                                                                                        

Creating & Altering Database Objects

Chapter Includes:

•

Adding columns to a table

•

Renaming objects

•

Temporary tables

•

Permanent Temporary tables

•

Views

•

View with check option

•

Rule

•

Defaults

•

User defined Objects

•

Constraints

___________________________________________________________________________________________________ Copyrights Reserved Page 2 of 10 

                                                                                                                                        

Adding columns to a table 

We use Alter table command for adding and removing the columns to the table.

Column Properties:  Null

: a value does not need to be specified for a column

 Not Null : a value must be specified for a column  Identity : server is to maintain a row counter on the table.

Renaming objects  To rename the object in the database such as table or a view we use sp_rename option.  Syntax : sp_rename old name, new name.

Temporary tables 

Temporary tables are the real tables created in the tempdb database, usually for the purpose of holding an intermediate result set. They can be identified by the symbol #.



They exist for a particular session.

They are 2 types



Temporary tables and



Permanent temporary tables

___________________________________________________________________________________________________ Copyrights Reserved Page 3 of 10 

                                                                                                                                        

Temporary tables: 

Temporary tables only last as long as the session exists and they are available only for your session.

Permanent temporary tables: 

They exist til the explicitly is dropped or the SQL server is restarted.

Views 

Views are logical ways of looking at the physical data located in the tables. It is used to limit access to selected columns in a base table. We can also limit the access to rows by using view with where clause, which is referred as view horizontal.



View with check option prevents the insertion or updating of rows that will subsequently not meet the view criteria.



Syntax : create view view name as select col_name from table name

Rule 

Rules provide a mechanism for enforcing domain constraints for columns or user defined data types.



They are applied before an insert or update, prior to the execution of the command.



Syntax : create rule rule name as @exp

___________________________________________________________________________________________________ Copyrights Reserved Page 4 of 10 

                                                                                                                                        

Defaults 

Defaults provide a value for a column when one is not supplied at insert time. They exist only as database objects, after creating, they must be subsequently bound to columns.



Syntax : create default default_name as expression.

User defined Objects 

User defined datatypes are not really a new data type, it is a way of describing an existing data type. it provides a mechanism for enforcing data type consistency across and within a database or server.



Syntax : sp_add type type_name sys name

Constraints 

Constraints provide an alternative method for defining data integrity requirements.

There are three types of constraints



Primary key and Unique key constraints



Check constraints



Referential integrity constraints

___________________________________________________________________________________________________ Copyrights Reserved Page 5 of 10 

                                                                                                                                         Primary key and Unique key constraints:



They require that all values in a table be unique and the columns cannot allow null values. Where as a Unique key constraint can allow one null value in its respective columns.

Check constraints: 

Similar to rules they specify a domain for columns. They are slightly faster.



They are defined for a table using the create table or alter table command. They can be defined at the column level or table level.

Referential integrity constraints:



It is the property of all foreign keys having associated with a primary key in their related tables. Constraints which enforce both primary and foreign key integrity checks without programming is called Declarative referential integrity.

Important Syntaxes a) Altering a table 

It means adding a new column to a table with the help pf alter command. Alter table tablename add (new column) int null.

b) Renaming an objects 

It means renaming an existing name in the table. sp_rename old_name, new_name.



To rename an existing column in the table the syntax is Sp_rename 'table_name.old_name',new_col_name

___________________________________________________________________________________________________ Copyrights Reserved Page 6 of 10 

                                                                                                                                         c) Views 

It is a logical way of looking at the physical data locatetd in the tables.The syntax is follows. Create view view_name as select column name from tablename.

d) Rules



It is used to assign a rule for the column. Create rule rule name as some condition (like @mark >50)



Next we have to bind the rule to the table sp_bindrule rulename,'tablename.columnname'

e) Default 

Default provide a value for a column when one is not supplied at the insertion time Create default default_name as constant expression

SESSION: 1) For the table win_ratio, 

Create Rule that if matches lost=3 then points= -3 and matches lost= 6 then points=-6



Create default that rank should be null if the points less than 0



Create a View that only teams and rank can be view.

___________________________________________________________________________________________________ Copyrights Reserved Page 7 of 10 

                                                                                                                                         2) Give the difference between the following. 

char and varchar



primary and unique constraints.



order by and group by.



temporary tables and permanent temporary tables

3) What happens when we fail to specify a common key while using join ?

4) What are user defined data types ? How many valid data types are used for storing strings? 5) State true or false 

T-SQL allows more than three keywords.



Data Definition Language is also used to secure the table data.



Distinct is the only keyword that removes duplicate rows from the result set.



count (*) returns the number of non null expressions .

6) What is the syntax to get a list of columns in a view?

7) What are the column properties?

8) What are foreign-key constraints ? How can u modify and add constraints?

___________________________________________________________________________________________________ Copyrights Reserved Page 8 of 10 

                                                                                                                                        

DATABASE SCHEMA

The database scheme consists of four relations: Product(maker,model,type) PC(code,model,speed,ram,hd,cd,price) Laptop(code,model,speed,ram,hd,screen,price) Printer(code,model,color,type,price) The relation "Product" shows the maker, model number, and type (pc, laptop, or printer). It is assumed that model numbers are unique for all the makers and product types. Each model number specifying pc in the relation "PC" is characterized by speed (of the processor in MHz), total amount of RAM (in Mb), hard disk drive capacity (in Gb), CD ROM speed (for example, '4x'), and the price. The relation "Laptop" is similar to that one of PCs except for the CD ROM speed, which is replaced by the screen size (in inches). For each printer model in the relation "Printer" it is told whether the printer is color or not (color attribute is 'y' for color printers; otherwise it is 'n'), printer type (laser, jet, or matrix), and the price.

___________________________________________________________________________________________________ Copyrights Reserved Page 9 of 10 

                                                                                                                                         Based on the above database scheme solve the following Queries 1. Find the model number, speed and hard drive capacity for all the PCs with prices below $500. Result set: model, speed, hd. 2. Find the printers having the highest price. Result set: model, price. 3. Find the model number, speed and hard drive capacity of the PCs having 12x CD and prices less than $600 or having 24x CD and prices less than $600. Topics Covered: Creating and Altering Database objects

Book for Reference Book: Sybase SQL Server 11 by Ray Rankins Jeffrey R. Garbus David Solomon Bennett Wm.McEwan

___________________________________________________________________________________________________ Copyrights Reserved Page 10 of 10