...Table Name : Employee EMPLOYEE _ID FIRST_NA ME LAST_NA ME SALA RY JOINING_D ATE DEPARTME NT 1 John Abraham 1000000 01-JAN-13 12.00.00 AM Banking 2 Michael Clarke 800000 01-JAN-13 12.00.00 AM Insurance 3 Roy Thomas 700000 01-FEB-13 12.00.00 AM Banking 4 Tom Jose 600000 01-FEB-13 12.00.00 AM Insurance 5 Jerry Pinto 650000 01-FEB-13 12.00.00 AM Insurance 6 Philip Mathew 750000 01-JAN-13 12.00.00 AM Services 7 TestName1 123 650000 01-JAN-13 12.00.00 AM Services 8 TestName2 Lname% 600000 01-FEB-13 12.00.00 AM Insurance Table Name : Incentives EMPLOYEE_REF_ID INCENTIVE_DATE INCENTIVE_AMOUNT 1 01-FEB-13 5000 2 01-FEB-13 3000 3 01-FEB-13 4000 1 01-JAN-13 4500 2 01-JAN-13 3500 SQL Queries Interview Questions and Answers on "SQL Select" 1. Get all employee details from the employee table Select * from employee 2. Get First_Name,Last_Name from employee table Select first_name, Last_Name from employee 3. Get First_Name from employee table using alias name “Employee Name” Select first_name Employee Name from employee 4. Get First_Name from employee table in upper case Select upper(FIRST_NAME) from EMPLOYEE 5. Get First_Name from employee table in lower case Select lower(FIRST_NAME) from EMPLOYEE 6. Get unique DEPARTMENT from employee table select...
Words: 4444 - Pages: 18
...In SQL, a join is used to compare and combine rows from two or more table. Inner Join: An inner join returns records only those records are common in both tables. Outer Join An outer join returns a set of records (or rows) that include what an inner join would return but also includes other rows for which no corresponding match is found in the other table. There are three types of outer joins: * Left Outer Join (or Left Join) * Right Outer Join (or Right Join) * Full Outer Join (or Full Join) Each of these outer joins refers to the part of the data that is being compared, combined, and returned. Sometimes nulls will be produced in this process as some data is shared while other data is not. Left Outer Join A left outer join will return all the data in Table 1 and all the shared data (so, the inner part of the Venn diagram example), but only corresponding data from Table 2, which is the right join. Right Outer Join A right outer join returns Table 2's data and all the shared data, but only corresponding data from Table 1, which is the left join. Full Outer Join A full outer join, or full join, which is not supported by the popular MySQL database management system, combines and returns all data from two or more tables, regardless of whether there is shared information. Think of a full join as simply duplicating all the specified information, but in one table, rather than multiple tables. Where matching data is missing, nulls will be produced. These...
Words: 288 - Pages: 2
...Project 6 Required Queries: * Create a query that includes at least two INNER JOINS (i.e. spans at least three tables), a search criteria, and orders the resulting data. * >select M.MID, M.MFName * >from MEMBER as M * >inner join LINEITEM as LI on LI.MID=M.MID * >inner join ITEM as I on I.IID=LI.IID * >inner join DVD as D on D.IID=I.IID; * This shows which members have checked out DVDs * Create a query that includes a calculation (e.g. average, sum, etc.). * >Select M.MID, M.MFName, count(B.IID) as “Books Checked Out” * >from MEMBER as M * >inner join LINEITEM as LI on LI.MID=M.MID * >inner join ITEM as I on I.IID=LI.IID * >inner join BOOK as B on B.IID=I.IID; * This query shows how many books a member has checked out * Create a query that includes at least one RIGHT or LEFT outer join. * >select MEMBER.MID, MEMBER.MFName, LIBRARIAN.LFName * >from MEMBER * >left outer join LINEITEM on LINEITEM.MID=MEMBER.MID * >left outer join LIBRARIAN on LINEITEM.LID=LIBRARIAN.LID * >order by MEMBER.MID; * This query uses a left join to show the relations between which librarians have checked out which members * Create a query that uses a GROUP BY to perform a calculation on information spanning at least two tables. * Create a query that includes a GROUP BY, but selects items from...
Words: 289 - Pages: 2
... SELECT o.OrderID, n.ContactName, e.FirstName AS EmployeeFirstName, e.LastName AS EmployeeLastName, o.OrderDate FROM Orders o JOIN Employees e ON (e.EmployeeID = o.EmployeeID) JOIN Customers n ON (n.CustomerID = o.CustomerID) WHERE o.OrderDate > '1998-03-01' ORDER BY n.ContactName; 2. SELECT COUNT(DISTINCT Employees.EmployeeID) AS NumberOfEmployees, ISNULL(Employees.Country, 'None.') AS Country, COUNT(DISTINCT Customers.CustomerID) AS NumberOfCustomers, Customers.Country FROM Employees RIGHT JOIN Customers ON Employees.Country = Customers.Country GROUP BY Customers.Country, Employees.Country ORDER BY NumberOfEmployees DESC, NumberOfCustomers DESC; ΕΡΩΤΗΜΑ Δ 1. SELECT Address, Code, Country FROM Suppliers UNION SELECT Address, Code, Country FROM Customers UNION SELECT Address, Code, Country FROM Employees ORDER BY Country; 2. SELECT AVG(OD.Quantity) AS AverageQuantity, SUM(OD.Quantity * OD.UnitPrice) AS TotalOrderAmount, DATEDIFF(yy, e.BirthDate, e.HireDate) AS HireAge FROM [Order Details] OD JOIN Orders o ON ( OD.OrderID =O.OrderID) JOIN Employees E ON ( E.EmployeeID =O.EmployeeID) WHERE DATEDIFF(yy, e.BirthDate, e.HireDate) > = 30 GROUP BY DATEDIFF(yy, e.BirthDate, e.HireDate) ORDER BY DATEDIFF(yy, e.BirthDate, e.HireDate) DESC; ΕΡΩΤΗΜΑ Ε 1. SELECT o.ShippedDate, o.OrderDate FROM Orders o JOIN Suppliers sp ON (o.EmployeeID = sp.SupplierID) WHERE sp.CompanyName LIKE '%Pavlova%' ; 2. SELECT c.CompanyName,...
Words: 298 - Pages: 2
...SELECT column_list FROM table-name [WHERE Clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause]; SELECT name, salary, salary*1.2 AS new_salary FROM employee WHERE new_salary /salary*1.2 > 30000; SELECT first_name, last_name, subject FROM student_details WHERE subject IN/NOT IN ('Maths', 'Science'); Select * from product p where EXISTS (select * from order_items o where o.product_id = p.product_id) ------------------------------------------------- SELECT user.name, course.name ------------------------------------------------- FROM `user` ------------------------------------------------- RIGHT JOIN `course` on user.course = course.id SELECT name, salary FROM employee ORDER BY 1, 2; ELECT name, salary FROM employee ORDER BY name DESC, salary DESC; SELECT COUNT (DISTINCT name) FROM employee; HAVING Group functions (MAX,MIN) cannot be used in WHERE Clause but can be used in HAVING clause. SELECT dept, SUM (salary) FROM employee GROUP BY dept HAVING SUM (salary) > 25000 INSERT INTO TABLE_NAME [ (col1, col2, col3,...colN)] VALUES (value1, value2, value3,...valueN); INSERT INTO employee (id, name, dept, age, salary location) SELECT emp_id, emp_name, dept, age, salary, location FROM temp_employee; Copy data from A to B. Select first then insert If you are inserting data to all the columns, the above insert statement can also be written as, INSERT INTO employee SELECT...
Words: 652 - Pages: 3
...ALTER PROCEDURE [dbo].[ASP_Weekly_Lift_Report] @Voyage_Start Varchar(3) = Null, @Voyage_End Varchar(3) = Null, @Sales_Rep Varchar(25) = Null, @Service Varchar(4) = Null AS SET @Voyage_Start = IsNull(@Voyage_Start, '%') SET @Voyage_End = IsNull(@Voyage_End, '%') SET @Sales_Rep = IsNull(@Sales_Rep, '%') SET @Service = IsNull(@Service, '%') -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT dbo.tbl_Lift_Report_PayorList.[Rep Name], dbo.tbl_Lift_Report_PayorList.Parent, dbo.tbl_Lift_Report_PayorList.Business_Type, dbo.tbl_Lift_Report_PayorList.PayorID, dbo.tbl_Lift_Report_PayorList.Payor, dbo.tbl_Lift_Report_PayorList.[Payor Name - ID], dbo.tbl_Lift_Report_PayorList.[Booking Payor], dbo.tbl_Lift_Report_PayorList.ShipperID, dbo.tbl_Lift_Report_PayorList.Shipper, dbo.tbl_Lift_Report_PayorList.ConsigneeID, dbo.tbl_Lift_Report_PayorList.Consignee, dbo.tbl_Lift_Report_PayorList.[Service], dbo.tbl_Lift_Report_PayorList.[Svc Desc], dbo.tbl_Lift_Report_PayorList.Vessel, dbo.tbl_Lift_Report_PayorList.Voyage, dbo.tbl_Lift_Report_PayorList.[Voyage No], dbo.tbl_Lift_Report_PayorList.[Cargo Type], dbo.tbl_Lift_Report_PayorList.Booking, dbo.tbl_Lift_Report_PayorList.[Profit Year], dbo.tbl_Lift_Report_PayorList...
Words: 356 - Pages: 2
...Estándares Transact-SQL Buenas Prácticas de Programación 1. Convenciones y Estándares de Nombres Nota: Los términos “notación Pascal” y “notación de Camell” son usados a través de este documento. Notación Pascal – El primer carácter de todas las palabras se escribe en Mayúsculas y los otros caracteres en minúsculas. Ejemplo: ColorDeFondo Notación de Camell – El primer carácter de todas las palabras, excepto de la primera palabra se escribe en Mayúsculas y los otros caracteres en minúsculas. Ejemplo: colorDeFondo 1. Usa notación Pascal para el nombre de las Tablas CREATE TABLE dbo.Employee 2. Usa notación Pascal para el nombre de los campos de tabla CREATE TABLE dbo.Employee ( IdEmployee INT, FirstName VARCHAR(20), LastName VARCHAR(20) ) 3. NO usar nunca “sp_”, La razón es que: SQL Server reconoce el prefijo “sp_” como “System Stored Procedure”, es decir, un procedimiento almacenado de Sistema y lo buscaría en la Base de Datos. Usa la siguiente sintaxis para los nombres de los Stored procedures: Usp_<Nombre Esquema>_<Nombre Tabla> _<Accion> Ejemplo: usp_GEN_Employee_Insert usp_GEN_Employee_GetAll 4. Usa esquemas para agrupar los objetos como tablas, los nombres deben de ser Abreviados. Incorrecto: GEN_Employee Correcto: GEN.Employee 2. Consideraciones en el diseño de base de datos 1. El nombre de la base de datos debe de asemejarse al nombre de la aplicación, no deberá de contener...
Words: 2608 - Pages: 11
...Which is not true about the USING keyword? A) you use it to simplify the syntax for joining tables B) you code a USING clause in addition to the ON clause C) it can be used with inner or outer joins D) the join must be an equijoin, meaning the equals operator is used to compare the two columns Points Earned: 0.0/2.0 Correct Answer(s): B Correct 2. When you code a SELECT statement, you must code the four main clauses in the following order A) SELECT, FROM, ORDER BY, WHERE B) SELECT, ORDER BY, FROM, WHERE C) SELECT, WHERE, ORDER BY, FROM D) SELECT, FROM, WHERE, ORDER BY Points Earned: 2.0/2.0 Correct Answer(s): D Correct 3. When coded in a WHERE clause, which search condition will return invoices when payment_date isn’t null and invoice_total is greater than or equal to $500? A) payment_date IS NULL AND invoice_total > 500 B) payment_date IS NOT NULL OR invoice_total >= 500 C) NOT (payment_date IS NULL AND invoice_total = 500 Points Earned: 2.0/2.0 Correct Answer(s): D Correct 4. The order of precedence for the logical operators in a WHERE clause is A) Not, And, Or B) And, Or, Not C) Or, And, Not D) Not, Or, And Points Earned: 2.0/2.0 Correct Answer(s): A Correct 5. Which of the following types of SQL statements is not a DML statement? A) INSERT B) UPDATE C) SELECT D) CREATE TABLE Points Earned: 2.0/2.0 Correct Answer(s): D Incorrect 6. Which of the following is not a common error when entering and...
Words: 1309 - Pages: 6
...Join in SQL SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. SQL Join is used for combining column from two or more tables by using values common to both tables. JoinKeyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is(n-1) where n, is number of tables. A table can also join to itself known as, Self Join. Types of Join The following are the types of JOIN that we can use in SQL. * Inner * Outer * Left * Right Cross JOIN or Cartesian Product This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. Cross JOIN Syntax is, SELECT column-name-list from table-name1 CROSS JOIN table-name2; Example of Cross JOIN The class table, ID | NAME | 1 | abhi | 2 | adam | 4 | alex | The class_info table, ID | Address | 1 | DELHI | 2 | MUMBAI | 3 | CHENNAI | Cross JOIN query will be, SELECT * from class, cross JOIN class_info; The result table will look like, ID | NAME | ID | Address | 1 | abhi | 1 | DELHI | 2 | adam | 1 | DELHI | 4 | alex | 1 | DELHI | 1 | abhi | 2 | MUMBAI | 2 | adam | 2 | MUMBAI | 4 | alex | 2 | MUMBAI | 1 | abhi | 3 | CHENNAI | ...
Words: 1005 - Pages: 5
...and the number of sections that they have taught. Make sure to show the number of sections as 0 for instructors who have not taught any section. Your query should use an outer join, and should not use scalar subqueries. By using the university schema provided by db-book.com the following queries were done on the university database. The first query uses an outer join which works similar to the join operation but it keeps the rows that don’t match between the two tables that would be lost in a join operation. There are three forms of outer join: a) Left outer join displays the results from the left table even if the condition does not find any matching record in the right table. b) Right outer join will displays the results from the right table regardless if there is matching data in the left table. c) Full outer join will retain all rows from both tables, regardless if the data matches or not. The group by clause when used in a select statement collects data from multiple records and groups the results into one or more columns. The below is the query using left outer join operation: select ID, name, count(sec_id) as Number_of_sections from instructor natural left outer join teaches group by ID, name; The next query was written by using a scalar subquery without using an outer join operation. A scalar subquery is where the output of a subquery returns only one row which contains one single column/attribute. By using the select statement with count (*)...
Words: 414 - Pages: 2
...name who are treated by Doctor Dr Bill. Sol.select p.fname,p.lname from patients p inner join doctors d on p.docid=d.doctorid where d.fname='dr.bill'; Q2. Find Patient Details occupied in room 1100. Sol.select p.patientid,p.fname,p.lname,p.address from patients p inner join room r on p.patientid=r.patientid where r.roomno=1100; Q3. Nurse Sue checked which all patients on 09-09-2015. Sol.select p.fname,p.lname from patients p where p.patientid in(select r.patientid from room r inner join nursecheckingdetails n on n.roomno=r.roomno where n.daate='09-09-2015'); Q4. List Names of Nurses who work with Dr Bill. Sol.select n.fname,n.lname from nurses n inner join doctors d on n.doctorid=d.doctorid and d.fname='dr.bill'; Q5. List Names of Doctors and number of patients they are treating or have treated. Sol.select d.fname as Doctorfname,d.lname as DoctorLName,count(p.patientid) as NoofPatients from patients p inner join doctors d on p.docid=d.doctorid group by d.fname,d.lname; Q6. List Names of Doctors and number of nurses they have with them to help. Sol.select d.fname as Doctorfname,d.lname as DoctorLname,count(n.nurseno) from doctors d left outer join nurses n on d.doctorid=n.doctorid group by d.fname,d.lname; Q7. How many rooms to type Basic are occupied? Sol.select count(r.roomno) as NoOfRooms from room r inner join roomdetails rd on r.typeofroom=rd.roomtypeid where rd.type='basic' and r.status='occupied'; ...
Words: 461 - Pages: 2
...euro was introduced in January 1999, the United Kingdom was conspicuously absent from the list of European countries adopting the common currency. Although the current Labor government led by Prime Minister Tony Blair appears to be in favor of joining the euro club, it is not clear at the moment if that will actually happen. The opposition Tory party is not in favor of adopting the euro and thus giving up monetary sovereignty of the country. The public opinion is also divided on the issue. Whether the United Kingdom will eventually join the euro club is a matter of considerable importance for the future of European Union as well as that of the United Kingdom. The joining of the United Kingdom with its sophisticated finance industry will most certainly help propel the euro into a global currency status rivaling the U.S. dollar. The United Kingdom on its part will firmly join the process of economic and political unionization of Europe, abandoning its traditional balancing role. Assignment Tasks Investigate the political, economic and historical situations surrounding the British participation in the European economic and monetary integration and write your own assessment of the prospect of British joining the euro club. In dong so, assess from the British perspective, among other things, (1) potential benefits and costs of adopting the euro, (2) economic and political constraints facing the country, and (3) the potential impact of British adoption of the euro on the international...
Words: 265 - Pages: 2
...Written Assignment 3 Explain the SQL commands Union, Intersect, and Minus with concepts like union-compatibility, and syntax alternatives such as IN/NOT IN and various JOIN options Database Management CIS-311 There are many different SQL commands. This assignment will focus on UNION, INTERSECT, and MINUS. UNION combines unique rows returned by two SELECT statements. UNION ALL functions in the same way as UNION except that it also returns duplicates. INTERSECT gives you rows that are found in both queries by eliminating rows that are only found in one or the other. An INTERSECT is simply an inner join where we compare the tuples of one table with those of the other, and select those that appear in both while weeding out duplicates. MINUS returns the rows that are in the first query but not the second by removing the rows that are only found in the second query. There are three primary SQL commands involved when implementing a Union, Intersection and difference relational operators. As you may know, SQL data manipulation commands are set-oriented which are involved in operating over entire sets of rows and columns in tables at once. The UNION, INTERSECT, and MINUS statements make sure these operations occur. Union, Intersect and Minus only work properly if relations are Union-Compatible, which is based on the names of the relation attributes that must be the same and their data types must be alike. Being compatible does not mean the data types have to be exactly the...
Words: 673 - Pages: 3
...object-oriented programming is a way of modularizing common concerns. AspectJ is an implementation of aspect-oriented programming for Java A join point is a well-defined point in the program flow. A pointcut picks out certain join points and values at those points. A piece of advice is code that is executed when a join point is reached. These are the dynamic parts of AspectJ. AspectJ's aspect are the unit of modularity for crosscutting concerns. They behave somewhat like Java classes, but may also include pointcuts, advice and inter-type declarations. AspectJ provides for many kinds of join points, but this chapter discusses only one of them: method call join points. A method call join point encompasses the actions of an object receiving a method call. It includes all the actions that comprise a method call, starting after all arguments are evaluated up to and including return (either normally or by throwing an exception). when a particular method body executes execution(void Point.setX(int)) when a method is called call(void Point.setX(int)) when an exception handler executes handler(ArrayOutOfBoundsException) when the object currently executing (i.e. this) is of type SomeType this(SomeType) when the target object is of type SomeType target(SomeType) when the executing code belongs to class MyClass within(MyClass) when the join point is in the control flow of a call to a Test's no-argument main method cflow(call(void Test.main())) Pointcuts compose through...
Words: 876 - Pages: 4
...Internals Neil Conway neilc@samurai.com November 10, 2003 Preamble These notes were originally written for my own use while taking CISC-432, a course in DBMS design and implementation at Queen’s University. The textbook used by that class is Database Management Systems, 3rd Edition by Raghu Ramakrishnan and Johannes Gehkre; some of the material below may be specific to that text. This document is provided in the hope that it is useful, but I can’t provide any assurance that any information it contains is in any way accurate or complete. Corrections or additions are welcome. Distribution Terms: This document is released into the public domain. Query Evaluation External Sorting • A DBMS frequently needs to sort data (e.g. for a merge-join, ORDER BY, GROUP BY, etc.) that exceeds the amount of main memory available. In order to do this, an external sort algorithm is used. • 2-Way External Merge Sort: – In the first pass, each page of the input relation is read into memory, sorted, and written out to disk. This creates N runs of 1 page each. – In each successive pass, each run is read into memory and merged with another run, then written out to disk. Since the number of runs is halved with every pass, this requires log2 N passes. Since an additional initial pass is required and each pass requires 2N I/Os, the total cost is: 2N( log2 N + 1) – Thus, we can see that the number of passes we need to make is critical to the overall performance of the sort (since in each pass, we read...
Words: 12979 - Pages: 52
