Results 1 to 2 of 2
Thread: Type The basic queries of Sql?
Jun 1, 2003, 00:29 #1
Type The basic queries of Sql?
Please post all basic queries of sql (Oracle SQL )
1: how to make table?
2: define the the data type of table
3: insert data into field
and so on
try posting as much queries as u can it will really help all biggners.
Jun 1, 2003, 02:03 #2
- Join Date
- Mar 2002
- 0 Post(s)
- 0 Thread(s)
Creating a table has several options since sometimes you need constraints (FK, PK, check) and also because column types differ. (of course the syntax remains the same)
Example: A company has employees who work on zero or more projects. A projects can have multiple employees.
-- table projects CREATE TABLE projects ( projid INTEGER PRIMARY KEY NOT NULL, description VARCHAR2(40) ); -- table employees CREATE TABLE employees ( empid INTEGER PRIMARY KEY NOT NULL, salary NUMERIC(7,2), position VARCHAR2(30) CHECK position IN ('projectleader', 'employee') ); -- table proj_empl CREATE TABLE proj_empl ( project INTEGER References projects(projid) ON DELETE CASCADE, employee INTEGER References employees(empid) ON DELETE CASCADE, PRIMARY KEY (project, employee) );
-- inserting only in some of the columns -- it is necessary to list the columns INSERT INTO projects (description) VALUES ('blahblah'); -- inserting in all columns -- not needed to list all columns INSERT INTO proj_empl VALUES (1, 1);
-- delete the employee with the highest salary DELETE FROM employees WHERE empid = (SELECT e.empid FROM employees e WHERE salary = (SELECT Max(salary) FROM employees e2) );
-- the salaries below the average will be doubled UPDATE employees SET salary = salary * 2 WHERE salary < (SELECT Avg(salary) FROM employees e1);