Hi Friends, I would like join to this group study.. anu333, please send me the materials.. I will try to catch up with you all in couple of days. Thanks, Jothi.
Questions: 1.What is the difference between unique+not null and primary key constraint? 2.Explain default constraint 3.What are the restrictions of a foreign key constraint? 4.what happens to index when you drop a primary key constraint 5.what is the difference between equi join and outer join? 6.Explain Self join with example Answers: 1. Unique not null and primary key constraints - Both doesnt allow null value and values in that column should be unique but primary key constraint will have default clustered index, ie. data is sorted based on the primary key column. 2. Default constraint - Column can be defined with default value which will be inserted to the table if the value is not passed to that column. 3. Restrictions of Foreginkey - I guess, you can create foreign key referencing only to the primary key of the another table but not just any column of table. 4. Both Primarykey constraint and index drops 5. Equi join = Data in both the tables are compared and validated against the condition. So, Query returns rows only if both the tables has same data. OuterJoin - It could either right, left or full outer join, so irrespective of condition, query result all data from both the tables for the full outer join, it just replaces null for unmatched rows. 6. Self Join - With Equi and outer join, it required 2 different tables to compare but with Self join, it compares data to itself. Best example: Find the Manager and its subordinates. Table Employee has EmployeeID, EmployeeNAme ManagerID SELECT m.EmployeeName ManagerName, e.EmployeeName SubOrdinates FROM Employee m JOIN Employee e ON m.EmployeeID = e.ManagerID WHERE m.EmployeeID <> e.EmployeeID Query to test: create table Employee ( EmployeeID int constraint pk_EmployeeID primary key, EmployeeNAme varchar(50), ManagerID int ) insert into Employee values (1, 'Manager', 0) insert into Employee values (2, 'SalesManager', 1) insert into Employee values (3, 'MarketingManager', 1) insert into Employee values (4, 'FinanceManager', 1) insert into Employee values (5, 'SalesRep1', 2) insert into Employee values (6, 'SalesRep2', 2) insert into Employee values (7, 'SalesRep3', 2) insert into Employee values (8, 'MarketingRep1', 3) insert into Employee values (9, 'MarketingRep2', 3) insert into Employee values (10, 'FinanceRep1', 4) insert into Employee values (11, 'FinanceRep2', 4) insert into Employee values (12, 'FinanceRep3', 4) insert into Employee values (13, 'FinanceRep4', 4) SELECT m.EmployeeName ManagerName, e.EmployeeName SubOrdinates FROM Employee m JOIN Employee e ON m.EmployeeID = e.ManagerID WHERE m.EmployeeID <> e.EmployeeID
Awesome Prtywomen, thnks so much for the reply..... Also, I would like to add more 1.when you talk about the differences between unique+not null compared to Primary Key, The performance is low with Unique+Not Null as compared to primary key! Also, Primary key is used for referential integrity while Unique+Not null is only for data integrity and validations. 3. For the restrictions for a foreign key constraint, the child and parent table must be on the same Db and the composite FK cannot have more than 32 columns and it cannot be applied on tables with datatypes as LOB,LONG,VARRAY,Nested table etc
Now, questions for Day 3 1. Can alias names be used in order by clause? 2. What is the difference between where and having clause? 3. What are the guidelines to use Group by clause? 4. What are the different aggregate functions? 5. What is the difference between count(columnname) and count(*)? 6. Why is having clause used after group by clause? The above are few questions, I could think of from the days topic, the next topic is Subqueries, the tricky one! All geared up for the prep!
Just my 2 cents. Please correct my understanding Case expression is not limited to find out non null expression. It is used to construct a if-else loop in a query itself. coalesce is to return first non-null epxression as said above. nvl will let you substitute a value if a null is encountered. nvl('test string',value_if_null) nvl2 extends the nvl function and let you substitute 1 value if null is encountered and another value for not null nvl2('test string',value_if_not_null,value_if_null)
just my 2 cents. learn/practice Oracle SQl/PLSQL on Linux or Unix ( U can use any flavor of Linux or UNIX distribution) & not on windows. Along with PL/SQL try to learn Unix/Linux Shell scripting. its a good combination. even the pay & opportunities are lot better 1. www.ubuntu.com 2. www.opensuse.org/ 3. http://www.debian.org/ 4. http://fedoraproject.org/ 5. http://hub.opensolaris.org/bin/view/Main/ http://en.wikipedia.org/wiki/List_of_Linux_distributions
HI Anu i am delayed here in study process due to long weekend now i am on track....here are my anws in RED.... 1. Can alias names be used in order by clause? Alias Names cannot be used in any of the clauses like Order BY, Having or group by....when we need to use the pupose of writing alias names, work around may be choosing sub-queries Eg: SELECT vendorname, invqty, invavg FROM ( select vendorname, count(*) as invqty, avg(invoicetotal) as invavg from vendors join invoices on vendors.vendorid=invoices.vendorid group by vendorname ) x where invavg > 500 order by invqty desc; 2. What is the difference between where and having clause? "Where " clause is used to select the rows that are included in the query. for eg consider a table EMP, User need rows EMPNAME, EMPNO, SAL who belongs to couple of states for eg consider AP, TN...in this case Where clause works good.... Query: select EMPNAME, EMPNO, SAL from EMP where state IN (AP, TN); It retrieves the rows that are mentioned in the Query. When the requirement is like retrieve the Employee sal irresptive of persons and W.r.t(Group By) States...then Having Clause came into picture.... Query: select state, SUM(SAL) from EMP Group BY state Having SUM(SAL)>0 Order BY state; Here we will get output: State SUM(SAL) ------------------------------ AP 10000.00 TN 20000.00 ---> Another difference also is by using HAVING clause we can use aggregate functions as condition to filter the rows....where as we cannot use aggregate functions in "WHERE" clause. 3. What are the guidelines to use Group by clause? DEF: Group BY clause is used in a select statement to collect data across multiple records and group the results wrt one or more columns. syntax: select col1,col2...aggregate_func(expr) from Test where condition group by col1, col2..col_n; if we observe here aggr_func() is there...it reflects aggregate functions like SUM, COUNT, MIN, MAX.... Eg: select deptName, SUM(sales) as "total sales" from Order_details Group BY deptName; here we get output of each department sales information grouped by depatment name. 4. What are the different aggregate functions? SUM, MAX, MIN, COunt(*)... 5. What is the difference between count(columnname) and count(*)? count(*)---> this aggregate function is used to get the return all values in the columns including NULL values. eg: select custID, count(*) from items group by custID having count(*)>1; This will return all values of the colum custID which are null also. count(columnname)--> this will return records only where the specified column name is NOT NULL. eg: select custID, count(custID) from items group by custID having count(custID)>1; this will return only the records whose custID column is not NULL. 6. Why is having clause used after group by clause? The purpose of Group by clause is to display the results by grouping more than one column. And Having Clause is used to apply condition on that group, it may be single col condition or an aggregate function...
Hi Sravani, Thanks for the answers. I have a doubt in the above answer. What is a single column condition in having clause? Please explain with an example.
Sorry for usage of wrong word....i mean it as without aggregate functions...like as in below example: select col1 from Test Group By col1 Having Col1>1; No Aggregate function is used here......hope you got my point here.