SQL Commands [PDF]

Zitiervorschau

---

SQL COMMANDS SQL Languages: ● DDL: Data Definition Language ○ The DDL provides a set of definitions to specify the storage structure and access methods used by the database system. ● DML: Data Manipulation Language ○ Data Manipulation Language(DML) is a language that enables users to access or manipulate data as organized by the appropriate data model. ● DCL: Data Control Language ○ It is used to control access to the database (by GRANTing / REVOKing etc.) and therefore essential to the system. SQL DATA TYPES: int number varchar2 decimal date char

CREATE

CREATE TABLE employee ( ecode integer ename varchar2(20) sex varchar2(1) grade char (2), gross decimal ) ; create table MOVIE (Movie_no integer, Title varchar2(15), Type varchar2(10), Star varchar2(25), Price number(8,2))

To show

● desc tablename; (oracle only)

structure ALTER

● sp_help tablename (sql server) Alter tablel is to alter structure of table ALTER TABLE

ADD ; ALTER TABLE Emp ADD (tel number ) ; ALTER TABLE bts DROP COLUMN id ; alter table CUST add AGE integer; Alter table name: ALTER TABLE bts rename column name to bname

eg)

create table branch ( bid integer, name varchar2(10), add varchar2(20) ) desc branch; alter table branch add (telno number(8))

UPDATE:

Update table is to Update data of table UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;

Insert

Insert all values: insert into CUST values('A01','Border','Allan','SA',723622) Insert specific values: insert into CUST(Cust_id,Fname,Area,Phone_no)values('A05','Sachin','Dr',25348 9) Insert into table 1 from table 2: INSERT INTO table1 SELECT * FROM table2 WHERE pay>500;

Delete:

delete from INVOICE where Return_date < '10-jul-95'; Truncate: truncate table MOVIE;

SELECT

Select all: select*from CUSTOMER; Select Specific Attributes from table branch: SELECT name, bid FROM branch; Print w a specific sentence: create table INVOICE ( Inv_no varchar2(3), Movie_no integer, Cust_id varchar2(3), Issue_date Date, Return_date Date ); select 'The Invoice No. Of Customer Id.' + Cust_id + ' is ' + Inv_no +' and Movie No. Is ' + cast(Movie_no as varchar) from INVOICE; We can specify only CHAR, NCHAR, VARCHAR or NVARCHAR data types in the PRINT statement. In this case, it implicitly converts an integer value to the VARCHAR data type internally.

DISTINCT :

SELECT distinct names FROM Movie; Select count(distinct names) From movie;

AVG, SUM,COU NT,MIN,M AX,STDD EV,VARIA NCE:

Select MAX (Age) from STUDENT where Sex = "F" ; Select AVG (Fee) from STUDENT where Dateofadm