SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member unknowerror's Avatar
    Join Date
    Jun 2003
    Location
    World
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Type The basic queries of Sql?

    Please post all basic queries of sql (Oracle SQL )
    for example


    1: how to make table?
    2: define the the data type of table
    3: insert data into field
    4: delete
    5: update
    6: edit
    and so on
    try posting as much queries as u can it will really help all biggners.
    Thanks.

  2. #2
    SitePoint Wizard Chris82's Avatar
    Join Date
    Mar 2002
    Location
    Osnabrück
    Posts
    1,003
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    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.

    Code:
    -- 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)
    );
    3.

    Code:
    -- 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);
    The first approach, specifying the columns is probably better, because if you add a column to the table the second way would not work anymore.

    4.

    Code:
    -- 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)
    );
    5.

    Code:
    -- the salaries below the average will be doubled
    UPDATE employees SET salary = salary * 2 
    WHERE salary < (SELECT Avg(salary) FROM employees e1);
    6. for data see 5


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •