`

SQL Basic Example

 
阅读更多

对Oracle 11g scott用户下的4张表进行数据查询

 

SET linesize 500;
SET pagesize 100;

DESC dept;

DESC emp;

DESC bonus;

DESC salgrade;

--基本查询
SELECT * FROM dept;

SELECT * FROM emp;

SELECT * FROM bonus;

SELECT * FROM salgrade;

SELECT deptno, dname FROM dept;

SELECT DISTINCT deptno, job FROM emp;

SELECT ename, hiredate FROM emp
WHERE hiredate > '01-JAN-82';

SELECT ename, hiredate FROM emp
WHERE hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD');

SELECT deptno, sal, job, ename FROM emp
WHERE deptno = 20 AND (sal > 2500 OR job = 'CLERK');

SELECT deptno, sal, job, ename FROM emp
WHERE deptno IN(20, 30) AND job NOT IN('CLERK', 'SALESMAN');

SELECT sal, hiredate, comm, ename FROM emp
WHERE sal BETWEEN 2500 AND 3500
	AND hiredate BETWEEN TO_DATE('1981-01-01', 'YYYY-MM-DD') AND TO_DATE('1981-12-31', 'YYYY-MM-DD')
	AND comm IS NULL;

SELECT ename, sal, deptno FROM emp
WHERE ename LIKE 'A%' OR ename LIKE '_A%';

SELECT ename, job, TO_CHAR(hiredate, 'YYYY-MM-DD'), sal * 1.2
FROM emp
WHERE hiredate > TO_DATE('1982-01-01', 'YYYY-MM-DD');

SELECT rowid, rownum, ename, sal, deptno FROM emp
WHERE ename LIKE 'A%' OR ename LIKE '_A%';

SELECT deptno, ename, sal, comm FROM emp
WHERE sal BETWEEN 1500 AND 3000
ORDER BY deptno DESC, ename;

SELECT DISTINCT deptno , job FROM emp
WHERE deptno = 20
ORDER BY job;

--分组查询
SELECT empno, sal , comm
FROM emp WHERE deptno = 30;

SELECT avg(sal), avg(distinct sal), max(sal), min(sal), sum(sal), 
	count(*), count(sal), count(distinct sal), count(comm)
FROM emp WHERE deptno = 30;

SELECT deptno, avg(sal), max(sal) FROM emp
GROUP BY deptno
ORDER BY avg(sal);

SELECT deptno, job, avg(sal), max(sal) FROM emp
GROUP BY deptno, job;

SELECT deptno, job, avg(sal), max(sal) FROM emp
GROUP BY rollup(deptno, job);

SELECT deptno, job, avg(sal), max(sal) FROM emp
GROUP BY cube(deptno, job);

SELECT deptno, avg(sal), max(sal) FROM emp
WHERE deptno <= 50
GROUP BY deptno
HAVING avg(sal) > 2000;

--连接查询
SELECT deptno FROM dept WHERE deptno < 30;

SELECT deptno, ename FROM emp WHERE job = 'CLERK';

SELECT d.deptno, e.deptno, e.ename 
FROM dept d, emp e
WHERE d.deptno < 30 AND e.job = 'CLERK';

SELECT d.deptno, d.dname, e.ename, e.sal
FROM dept d, emp e
WHERE d.deptno = e.deptno AND d.deptno = 20;

SELECT e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND e.deptno = 30;

SELECT empno, ename, mgr FROM emp
WHERE deptno = 30;

SELECT e.ename, m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno AND e.deptno = 30;

--集合查询
SELECT empno, ename, mgr FROM emp WHERE deptno = 30
UNION ALL
SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER';

SELECT empno, ename, mgr FROM emp WHERE deptno = 30
UNION
SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER';

SELECT empno, ename, mgr FROM emp WHERE deptno = 30
MINUS
SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER';

SELECT empno, ename, mgr FROM emp WHERE deptno = 30
INTERSECT
SELECT empno, ename, mgr FROM emp WHERE job = 'MANAGER';


--子查询
SELECT ename, deptno, sal FROM emp
WHERE sal = (SELECT max(sal) FROM emp);

SELECT ename, deptno, sal, job FROM emp
WHERE job IN(SELECT distinct job FROM emp WHERE deptno = 20);

SELECT ename, deptno, sal, job FROM emp
WHERE job NOT IN(SELECT distinct job FROM emp WHERE deptno = 20);

SELECT ename, deptno, sal, job FROM emp
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 20);

SELECT ename, deptno, sal, job FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 20)
ORDER BY deptno;

SELECT ename, deptno, sal, job FROM emp
WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');

SELECT ename, deptno, sal, job, mgr FROM emp
WHERE job IN(SELECT job FROM emp WHERE deptno = 20)
	AND mgr IN(SELECT mgr FROM emp WHERE deptno = 20)
ORDER BY deptno;

SELECT deptno, (
	SELECT max(sal) FROM emp b 
	WHERE b.deptno = a.deptno) maxsal
FROM emp a
ORDER BY deptno;

SELECT ename, deptno, sal, job FROM emp
WHERE EXISTS(
	SELECT 'x' FROM dept
	WHERE dept.deptno = emp.deptno AND dept.loc = 'NEW YORK');
	
SELECT distinct deptno, (
	SELECT max(sal) FROM emp b
	WHERE b.deptno = a.deptno) maxsal
FROM emp a
ORDER BY deptno;

 将以上语句放入一个sql文件中,如sqldemo.sql,在sqlplus中使用@ ${filepath}\sqldemo.sql命令执行查看结果。

 

 

分享到:
评论

相关推荐

    Python数据科学速查表 - Spark SQL 基础1

    .appName("Python Spark SQL basic example") \ .config("spark.some.config.option", "some-value") \ .getOrCreate() ``` SparkSession是Spark 2.0引入的新接口,它整合了HiveContext、SQLContext和...

    Oracle Database 10g SQL_code

    │ BasicExample1.java │ BasicExample2.java │ BasicExample3.java │ ├─sample_files │ binaryContent.doc │ textContent.txt │ └─SQL collection_schema.sql collection_schema_10g.sql ...

    python+spark+大数据

    .appName("Python Spark SQL basic example") \ .getOrCreate() # 读取 CSV 文件 data = spark.read.format("csv").option("header", "false").load("path/to/file.csv") ``` ### 3. 数据处理 题目要求完成多项...

    BASIC4Android的数据库范例源代码

    在移动应用中,数据库通常用于存储和管理数据,SQLExample是B4A中的一个典型数据库操作示例,它涵盖了如何在Android应用中创建、查询、更新和删除数据的基本步骤。下面,我们将深入探讨这些关键知识点。 首先,我们...

    Visual Basic 100例(1-50)

    6. **example32**:可能涉及到数据库访问,例如使用ADO.NET连接SQL Server,执行SQL查询,或者更新数据。这将涵盖`SqlConnection`,`SqlCommand`,以及数据绑定技术。 7. **EXAMPLE7**:可能是一个关于定时器控件...

    Beginning T-SQL with Microsoft SQL Server 2005 and 2008

    Transact-SQL, or T-SQL, is Microsoft Corporation’s powerful implementation of the ANSI standard SQL ...An example using the AdventureWorks2008DW database for SQL Server 2008 is also used in Chapter 15.

    在SQL数据库所有表中搜索数据

    例如,如果搜索关键字是“example”,则查询可能是`SELECT * FROM TableName WHERE ColumnName LIKE '%example%'`。 4. 执行查询:使用SqlCommand对象的ExecuteReader方法执行每个查询,并将结果存储在一个...

    SQL Practice Problems

    It's easy to find basic SQL syntax and keyword information online. What's hard to find is challenging, well-designed, real-world problems—the type of problems that come up all the time when you're ...

    SQL UPDATE ACCESS

    在ACCESS中,你可以使用VBA(Visual Basic for Applications)进行更高级的数据操作,但基础的更新操作可以通过SQL语句完成。下面我们将详细讨论如何执行这些操作,并探讨一些相关的知识点。 1. **SQL UPDATE语句**...

    instantclient-basic-win-x86-64-11.1.0.7.0.zip

    这个压缩包 "instantclient-basic-win-x86-64-11.1.0.7.0.zip" 包含了Windows 64位平台上的基本组件,用于在没有完整Oracle客户端安装的情况下连接到Oracle 11g数据库。它由Oracle公司提供,适用于那些需要快速、...

    Alpha Five SQL Database Application Architecture.pdf

    Note: This article assumes a basic knowledge of SQL Data Manipulation Language (DML) and Data Definition Language (DDL) and the ability to read simple code examples written in Xbasic.

    Example060-获得表的索引信息.rar

    在VB(Visual Basic)编程环境中,获取数据库表的索引信息是一项重要的任务,尤其是在进行数据操作和优化查询性能时。本示例"Example060-获得表的索引信息"将向我们展示如何实现这一功能。索引是数据库管理系统中的...

    SAS.9.2.SQL.Procedure.Users.Guide

    The example tables used throughout the guide are designed to demonstrate various aspects of using PROC SQL. These tables often include a mix of numeric and character fields and represent real-world ...

    sql-parser:C ++SQL解析器。 从SQL语句构建C ++对象结构

    C ++ SQL解析器 这是C ++SQL解析器。 它将给定SQL查询解析为C ++对象。 它是为与集成而开发的,但也可以在其他环境中完美使用。 在2015年3月,我们还撰写了一篇简短的论文,概述了... // Basic Usage Example con

    VisualBasic-使用VisualBasic开发的Mysql数据库.zip

    command.Parameters.AddWithValue("@email", "test@example.com") command.ExecuteNonQuery() ``` 这里,我们创建了一个`SqlCommand`,设置SQL语句,并添加了参数化输入。`ExecuteNonQuery()`方法执行非查询操作,...

    Example053-数据显示与修改.rar

    在VB(Visual Basic)编程环境中,数据显示与修改是常见的任务,尤其在开发用户界面或数据库应用时。"Example053-数据显示与修改.rar"这个压缩包文件很可能包含了一个示例项目,演示了如何在VB中实现这些功能。让...

    Visual Basic 2005文件IO与数据存取秘诀(数据库连接)

    Using reader As New StreamReader("C:\example.txt") Dim line As String = reader.ReadLine() Console.WriteLine(line) End Using ``` 这段代码将打开并读取指定路径的文件,然后打印出第一行内容。 2. **...

    Visual Basic 100例(51-100)

    4. example75:可能涉及数据库连接,例如使用ADO.NET访问SQL Server或其他数据库,学习如何执行查询、更新数据等操作。 5. example99:可能是一个综合性的案例,例如游戏或模拟器,展示VB的高级功能和性能优化技巧...

    oracle basic knowlage

    Oracle是世界上最广泛使用的数据库管理系统之一,它以其强大的功能和稳定性在企业级应用中...为了更深入地学习Oracle,你可以进一步探索这些主题,同时实践`oracle_basic_example.sql`中的示例代码,以巩固你的理解。

    微软内部资料-SQL性能优化3

    In our example, if one transaction (T1) holds an exclusive lock at the table level, and another transaction (T2) holds an exclusive lock at the row level, each of the transactions believe they have ...

Global site tag (gtag.js) - Google Analytics