Summary Notes.docx - CIS332 Summary SQL Summary of Commands...

Info icon This preview shows pages 1–2. Sign up to view the full content.

CIS332 Summary SQL Summary of Commands Chapter 2 p 26 Command Description Basic Syntax Structure Examples Command to view all columns of a table SELECT * FROM tablename ; SELECT * FROM books; Command to view one column of a table SELECT columnname FROM tablename ; SELECT title FROM books.; Command to view multiple columns of a table SELECT columnname, columnname FROM tablename; SELECT title, pubdate FROM books; Command to assign an alias to a column during display SELECT columnname [AS] alias FROM tablename ; SELECT title AS titles FROM books; or SELECT title titles FROM books; Command to perform arithmetic operations during retrieval SELECT arithmetic expression FROM tablename ; SELECT retail-cost FROM books; Command to eliminate duplication in output SELECT DISTINCT columnname FROM tablename or SELECT INIQUE column FROM tablename ; SELECT DISTINCT state FROM customers; or SELECT UNIQUE state FROM customers; Command to perform concatenation of column contents during display SELECT colunname || columnname FROM tablename ; SELECT firstname || lastname FROM customers; Command to view the structure of a table DESC tablename ; or DESCRIBE tablename ; DESCRIBE books or DESC books Commands and Clauses Description Chapter 3 p 92 Creating Tables CREATE TABLE Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the command DESCRIBE. CREATE TABLE acctmanager (amid VARCHAR2(4), amname VARCHAR2(20), antedate DATE DEFAULT SYSDATE, region CHAR(2)); CREATE TABLE. . . AS (. . .) Creates a table from existing database tables, using the AS clause and subqueries CREATE TABLE customerorder AS (SELECT customer#, orderdate, shipdate FROM orders); Modifying Tables ALTER_TABLE. . . ADD Adds a column to a table ALTER TABLE acctmanager ADD (ext NUMBER (4)); ALTER_TABLE. . . MODIFY Changes a column size datatype or default value ALTER TABLE acctmanager MODIFY (amname VARCHAR2(25)); ALTER_TABLE. . . DROP COLUMN Deletes one column from a table ALTER TABLE acctmanager DROP COLUMN ext; ALTER_TABLE. . . SET USED or ALTER_TABLE. . . SET UNUSED COLUMNS Marks a column for deletion at a later time ALTER TABLE cust_mkt SET UNUSED (state); DROP UNUSED COLUMNS Completes the deletion of a column previously marked with SET UNUSED ALTER TABLE cust_mkt DROP UNUSED COLUMNS; RENAME…TO Changes a table name RENAME cust_mkt TO cust_mkt_092009; TRUNCATE TABLE Deletes all table rows, but the table name and columns structure remain TRUNCATE TABLE cust_mkt_092009; Deleting Tables DROP TABLE… PURGE Removes an entire table from the Oracle 12c database DROP TABLE cust_mkt_092009 PURGE; PURGE TABLE
Image of page 1

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

Image of page 2
This is the end of the preview. Sign up to access the rest of the document.
  • Spring '16
  • kelly jo sanders

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern