Skip to content

Latest commit

 

History

History
187 lines (132 loc) · 6.08 KB

lab6_3-9.md

File metadata and controls

187 lines (132 loc) · 6.08 KB
Raw

-- Ingredients supplied by vrs CREATE VIEW vrs AS SELECT ingredientid, name, inventory, inventory * unitprice AS value FROM ingredients i, vendors v WHERE i.vendorid = v.vendorid AND companyname = 'Veggies_R_Us';

Select * from vrs;

-- > Changes in table also affect views. UPDATE ingredients SET inventory = inventory * 2 WHERE ingredientid = 'TOMTO';

select * from vrs;

-- lists all the items we have for sale inc meals and items, and how much they cost. CREATE VIEW menuitems (menuitemid, name, price) AS (SELECT m.mealid, m.name, CAST(SUM(price * (1-discount)) AS NUMERIC(5,2)) FROM meals m LEFT OUTER JOIN partof p ON m.mealid = p.mealid LEFT OUTER JOIN items i ON p.itemid = i.itemid GROUP BY m.mealid, m.name) UNION (SELECT itemid, name, price FROM items);

select * from menuitems; select name, price from menuitems where price = (select max(price) from menuitems); select count(*) from menuitems where price is null; --these more complex without views

--------------------- RESTRICTIONS ON DML OPERATIONS FOR VIEWS -- Restrictions on DML operations for views use the following criteria in the order listed:

--o If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, -- or a group function, then rows cannot be inserted into, updated in, or deleted from the -- base tables using the view.

--o If a view is defined with WITH CHECK OPTION, a row cannot be inserted into, or updated -- in, the base table (using the view), if the view cannot select the row from the base table.

--o If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then -- a row cannot be inserted into the base table using the view.

--o If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", -- ...), then rows cannot be inserted into or updated in the base table using the view.

SELECT owner, view_name, column_name, updatable FROM ALL_UPDATABLE_COLUMNS WHERE VIEW_NAME = 'vrs';

-- alter vrs to display ingredients supplied by SpringWaterSupply CREATE OR REPLACE VIEW vrs AS SELECT ingredientid, name, inventory, inventory * unitprice AS value FROM ingredients i, vendors v WHERE i.vendorid = v.vendorid AND companyname = 'Spring Water Supply';

select * from vrs; drop view vrs;

------------------------------EXERCISES------------------------------- create or replace view rsemp as select e.employeeid, e.firstname, e.lastname, (w.assignedtime/t.total)*100 as percentTime from employees e, workson w, projects p, (select e.employeeid as empid, sum(w.assignedtime) as total from employees e, workson w where e.employeeid = w.employeeid group by w.employeeid ) t -- total time employee sepends on all the projects where e.employeeid = w.employeeid and w.projectid = p.projectid and t.empid = e.employeeid and p.description = 'Robotic Spouse';

select * from rsemp;

Alter Table Tablename Disable Constraint Constraint-Name <--- select * from user_constraint;

Select * From rsemp;

Create View Empd As Select E.Employeeid, E.Firstname||' '||E.Lastname As Fullname, E.Salary, E.Deptcode, D.Name, D.Managerid, D.Subdeptof From Employees E, Departments D where E.deptcode = D.code;

Select * From Empd;

Update Empd Set Salary = Salary*1.1; Select * From Employees; select * from empd;

-------------------------END OF EXERCISE-------------------------------

Select 5-6+8/2*2 As "example Eqn" From Vendors Where Referredby Is Not Null; --5 rows satisfy this so 5 output rows come

Select SQRT(2) As "example Eqn" From Vendors where referredby is not null; --5 rows satisfy this so 5 output rows come

Select Name, InventoryUnitprice As Dollars, Ceil(Inventory * Unitprice67.08) As Rupees, 67.08 As "Exchange rate" from ingredients;

-- STRING Operatiions

--concat Select Manager, Address||' '||City||' '||State||' '||Zip||' USA' As Mail From Stores; --char padded with spaces, varchar not

--substr Select Substring(Repfname From 1 For 1)||'. '||Replname As Name From Vendors; -- WONT WORK ON SQL DEVELOPER

select substr('SAFETY', 2, 4) "Substring" from dual; --name, start, len

--trim SELECT DISTINCT ingredientid, foodgroup || '.' AS "with trailing", TRIM(TRAILING ' ' FROM foodgroup) || '.' AS "without trailing" From Ingredients WHERE inventory > 500;

-- TRIM ([[LEADING|TRAILING|BOTH] [] FROM] )

--LTRIM - remove from left until encounter a target string that doesnt exist in target set of char --ltrim(char, set)

Select Ltrim('NISHANT', 'N') "LTRIM" From Dual; --ISHANT

--RTRIM same

--UPPER AND LOWER --LOWER() same upper Select Upper(Repfname||' '||Replname) As Rep, Lower(Companyname) As Company from vendors where referredby = 'VGRUS';

--INITCAP function - modify char set values. Cap 1st letter Select Initcap('GEORGE BUSH') "title" From Dual;

--POSITION - find where substr begins ( in ) Select Name, Position('Salad' In Name) From Items; --WONT WORK --INSTR - str, target set of char select instr('george bush', 'us') "instr" from dual;

--CHAR[ACTER]_LENGTH Select Name, Char_Length(Name) As Namelen, Char_Length(Foodgroup) As Fglen From Ingredients; --WONT WORK --LENGHT select length('SHARMA12') from dual; --8

--LPAD Function - pad left side of a char string. makes each target string same overall len Select Lpad('page 3', 12, '*') "lpad" From Dual; --rpad same

--TRANSLATE - replace with another set of char, char by char (target, search, replace) Select Translate('1ddct568', '158', '249') "change" From Dual;-- 1->2, 5->4, 8->9 --2ddct469 out

-----------TEMPORAL --CURRENT_TIME[(precision)] --CURRENT_DATE --CURRENT_TIMESTAMP[(precision]) --LOCALTIME[(precision]) --LOCALTIMESTAMP([precision]) SELECT name, dateadded, DATE '2005-01-02' - dateadded AS "Days on Menu" From Items;

--extract - get fields from temporal data(fieldname from source) -- YEAR, MONTH, DAT, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE. SELECT name, EXTRACT(YEAR FROM dateadded) AS year, Extract(Month From Dateadded + Interval '30' Day) As Month From Items;