一、原题 Where can sub queries be used? (Choose all that apply) A. field names in the SELECT statement B. the FROM clause in the SELECT statement C. the HAVING clause in the SELECT statement D. the GROUP BY clause in the SELECT statement E. the WHERE cla…
在这样一个场景,我 left join 了很多张表,用这些表的不同列来过滤,看起来非常合理 但是出现的问题是 left join 其中一张或多张表出现了笛卡尔积,且无法消除
FUNCTION fun_get_xxx_helper(v_param_1 VARCHAR2,v_param_2 VARCHAR2…
一、原题 View the Exhibit and examine the description of SALES and PROMOTIONS tables. You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values. Which DELETE …
在这样一个场景,我 left join 了很多张表,用这些表的不同列来过滤,看起来非常合理 但是出现的问题是 left join 其中一张或多张表出现了笛卡尔积,且无法消除
FUNCTION fun_get_xxx_helper(v_param_1 VARCHAR2,v_param_2 VARCHAR2…
一、原题 Which three statements are true regarding subqueries? (Choose three.) A. Subqueries can contain GROUP BY and ORDER BY clauses. B. Main query and subquery can get data from different tables. C. Main query and subquery must get data from the same …
一、原题 View the Exhibits and examine PRODUCTS and SALES tables. You issue the following query to display product name and the number of times the product has been sold: SQL>SELECT p.prod_name, i.item_cnt FROM (SELECT prod_id, COUNT(*) …
一、原题 View the Exhibit and examine the structure of CUSTOMERS and GRADES tables. You need to display names and grades of customers who have the highest credit limit. Which two SQL statements would accomplish the task? (Choose two.) A.SELECT custname,…
一、原题 Examine the structure of the PRODUCTS table: Name Null Type PROD_ID NOT NULL NUMBER(4) PROD_NAME VARCHAR2(20) PROD_STATUS VARCHAR2(6) QTY_IN_HAND …
一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. You issue the following SQL statement on the CUSTOMERS table to display the customers who are in the same country as customers with the last name KING and whose credit limit is l…
一、原题 Examine the structure of the CUSTOMERS table: Name Null Type CUSTNO NOT NULL NUMBER(3) CUSTNAME NOT NULL VARCHAR2(25) CUSTADDRESS VARCHAR2(35) CUST_CREDIT_LIMIT NUMBER…
一、原题 Which three statements are true regarding sub queries? (Choose three.) A. Multiple columns or expressions can be compared between the main query and sub query B. Main query and sub query can get data from different tables C. Sub queries can cont…
文章目录 1.基本查询回顾2.多表查询3.自连接4.子查询4.1单行子查询4.2多行子查询4.3多列子查询4.4在from子句中使用子查询4.5合并查询4.5.1 union4.5.2 union all 1.基本查询回顾
表的内容如下:
mysql> select * from emp;
----------------------------------…
0 总结
对于SQL:select * from student, (select * from score where sno > 2) s where student.sno s.sno;
pullup在pull_up_subqueries函数内递归完成,分几步:
将内层rte score追加到上层rtbable中:rte1是student、rte2带…
一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that have the same data types and size as the corresponding columns in the CUSTOMERS table. Evaluat…
一、原题 Which two statements are true regarding subqueries? (Choose two.) A. A subquery can retrieve zero or more rows. B. Only two subqueries can be placed at one level. C. A subquery can be used only in SQL query statements. D. A subquery can appear o…
一、原题 View the Exhibit and examine the structure of the PRODUCTS table. Evaluate the following query: SQL> SELECT prod_name FROM products WHERE prod_id IN (SELECT prod_id FROM products WHERE pro…
一、原题 View the Exhibit and examine the structure of the PROMOTIONS table. You have to generate a report that displays the promo name and start date for all promos that started after the last promo in the INTERNET category. Which query would give you t…
一、原题 View the Exhibits and examine the structures of the COSTS and PROMOTIONS tables. Evaluate the following SQL statement: SQL>SELECT prod_id FROM costs WHERE promo_id IN (SELECT promo_id FROM promotions …
一、原题 View the Exhibit and examine the structure of the PRODUCTS table. You want to display the category with the maximum number of items. You issue the following query: SQL>SELECT COUNT(*), prod_category_id FROM products GROUP BY prod_cate…
一、原题 View the Exhibit and examine the structure of the PRODUCTS table. Which two tasks would require subqueries? (Choose two.) A. Display the minimum list price for each product status. B. Display all suppliers whose list price is less than 1000. C.…
一、原题 View the Exhibit and examine the description for the PRODUCTS and SALES table.PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done fo…
一、原题 View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cust_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer …
一、原题 Which three statements are true about multiple-row subqueries? (Choose three.) A. They can contain a subquery within a subquery. B. They can return multiple columns as well as rows. C. They cannot contain a subquery within a subquery. D. They ca…
一、原题 View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables. You want to update the EMPLOYEES table as follows: -Update only those employees who work in Boston or Seattle (locations 2900 and 2700). -Set department_id for …
一、原题 Evaluate the following SQL statement: SQL>SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (SELECT cust_credit_limit FROM customers WHERE cust_city Singapore); Which sta…
in,any/some,all,exists (有信息返回1,没有返回0) 1.in(val1,val2,val3)
in 相当于要 查询的条件 val1 or 查询的条件 val1 ...
not in 不是in()里面的值
--查询有员工的部门名 查询出employees表中所有的部门id select DISTINCT department_id from employees…
Oracle数据库SQL语句(四)
子查询(嵌套查询) 1、单行子查询
SELECT * FROM stuWHERE ID > (SELECT ID FROM stu WHERE NAME 王五);2、多行子查询
--IN 在什么范围内
SELECT * FROM stuWHERE ID IN (SELECT ID FROM stu WHER…
一、原题 View the Exhibit and examine the structure of the CUSTOMERS table. Which two tasks would require subqueries or joins to be executed in a single statement? (Choose two)A. listing of customers who do not have a credit limit and were born before 198…
分组查询
group by 关键字
例子
1.查询各job_id的员工工资的最大值,最小值,平均值,总和 select job_id,max(salary),min(salary),avg(salary),sum(salary)from employeesgroup by job_id
输出:
JOB_ID …
一、原题 Which two statements about sub queries are true? (Choose two.) A. A sub query should retrieve only one row. B. A sub query can retrieve zero or more rows. C. A sub query can be used only in SQL query statements. D. Sub queries CANNOT be nested b…
比如:
UPDATE test.tb_vobile a set a.name 111 WHEREa.id (select max(id) id from test.tb_vobile)
报错:
[SQL]UPDATE test.tb_vobile a set a.name 111 WHEREa.id (select max(id) id from test.tb_vobile) [Err] 1093 - You cant specify targ…
有两个表如下;产品表,产品评论表; 查询全部产品信息和每种产品的评论数量; 这也是子查询的一种;
select * from product1;
select * from comment;
SELECT product1.*,(select count(id) from comment where product1…