Notes by Louisa
Notes by Louisa
Notes by Louisa
  • Introduction
  • Chapter1 Python Cheatsheet
    • Reference, Deep Copy and Shallow Copy
    • Iterators
    • List Comprehensions
    • Numpy
    • Pandas
    • Data Visualization
    • DateTime
    • Python Good to knows
  • Chapter2 Java Cheatsheet
    • Fundamentals to Java
    • Interface, Abstract Class, Access Modifier, Exceptions
    • Linked List and Java List
    • Java Queue, Stack and Deque
    • Binary Tree
    • Heap in Java
    • Map/Set/Hash
    • OOD
  • Chapter3 Algorithm
    • Fundamental Knowledge
    • Binary Search
    • Basic Sorting
    • Advanced Sorting
    • Linked List
    • Recursion 1
    • HashTable
    • Queue
    • Sliding Window
    • Stack
    • Binary Tree
    • Binary Search Tree
    • Heap
    • String
    • Graph Search DFS1 (Back Tracking)
    • Recursion II and Memoization
    • Dynamic Programming
    • Complete Binary Tree, Segment Tree, Trie Tree
    • Graph Search BFS
    • Graph Search BFS 2
    • Graph Search DFS2
    • Problems from 'JianZhi Offer'
    • Problems Categorized
    • Bit Operations
  • Chapter4 Model
    • Linear Regression
    • Logistic Regression
    • Regularization and Feature Selection
    • Model Evaluation
    • Nonlinear Models
    • PCA
    • Unsupervised Learning
    • Gradient Descent and Gradient Boosting
    • XG Boost and Light GBD
    • Deep Learning
    • Tensorflow/Keras
    • RNN
  • Chapter5 Statistics and A/B Testing
    • Inference about independence
    • Probability, Sampling and Randomization with Python
    • A/B Testing
    • Stats Interview Review
    • Statistics Glossary
  • Chapter6 SQL
    • Student Scores Query
    • Order Query
    • Movie Rating Query
    • Social-Network Query
    • LeetCode SQL题目总结
    • Spark SQL
  • Chapter7 Big Data and Spark
    • Introduction to Pyspark
    • Data Cleaning with Apache Spark
    • Feature Engineering with Pyspark
    • Building Recommendation Engines with Pyspark
    • Building Data Engineering Pipelines in Python
    • Hadoop MapReduce
    • Big Data Related Paper
  • Chapter8 Code Walk-Throughs
    • Python
    • R
    • Shell
  • Chapter9 Special Topics
    • Anomaly Detection
    • E Commerce
    • Supply Chain
    • Social Network Analysis
    • NLP intro
    • Time Series
    • Challenge Prophet with LSTM models
  • Project: The Winning Recipes to an Oscar Award
  • Project: A Crime Analysis of the Last Decade NYC
  • Project: Predict User Type Based on Citibike Data
  • GeoSpark/GeoSparkVis for Geospatial Big Data
  • Single Scattering Albedo
  • Sea Ice Albedo Retrievals
  • Lidar Project
Powered by GitBook
On this page
  1. Chapter6 SQL

Student Scores Query

PreviousChapter6 SQLNextOrder Query

Last updated 5 years ago

Homework/Practice

练习平台

student

Sno

Sname

Ssex

Sage

Sdept

9512101

李三

男

19

计算机系

course

Cno

Cname

HOURS

C01

计算机网络

90

SC

Sno

Cno

Grade

9512101

C01

90

#2
SELECT Sno, Cno, Grade
FROM SC 
WHERE Grade BETWEEN 70 AND 80

#3
SELECT Grade
FROM SC
WHERE Cno='C01'
ORDER BY Grade DESC
LIMIT 1

#4
SELECT Cname, Cno
FROM course
WHERE Cno in (SELECT DISTINCT(Cno) FROM SC)

#5
SELECT AVG(Grade), MAX(Grade), MIN(Grade)
FROM SC
WHERE SC.Cno='C01'

#6
SELECT Sdept, COUNT(Sno)
FROM student
GROUP BY Sdept

#7
SELECT Cname, COUNT(Sno), MAX(Grade)
FROM SC, course
WHERE SC.Cno in (SELECT DISTINCT(Cno) FROM SC) AND SC.Cno=SC.Cno
GROUP BY SC.Cno

SELECT Cname, COUNT(Sno), MAX(Grade)
FROM course
INNER JOIN SC
USING Cno
GROUP BY SC.Cno

#8
SELECT student.Sname, student.Sno, COUNT(Cno)
FROM student
INNER JOIN SC ON SC.Sno=student.Sno
GROUP BY student.Sname, student.Sno
ORDER BY COUNT(Cno)

#9 
SELECT COUNT (DISTINCT(Cno)), AVG(Grade)
FROM SC

#10
SELECT Sno, Sname, COUNT(Cno), AVG(Grade)
FROM student
INNER JOIN SC on Sno
GROUP BY Student.Sname
HAVING COUNT(Cno)>2

#11
SELECT Sno, SUM(Grade) AS TOTAL
FROM student
INNER JOIN SC
USING Sno
GROUP BY student.Sno
HAVING TOTAL>200

#12 
SELECT Sname, Sdept
FROM student
INNER JOIN SC
USING Sno
WHERE SC.Cno='C02'


SELECT Sname, Sdept
FROM student
WHERE student.Sno==SC.Sno AND Cno='C02'

#13
SELECT Sname, Cno, Grade
FROM student
INNER JOIN SC
USING Sno
WHERE Grade>80
ORDER BY Grade DESC 

#14
SELECT Sname, Ssex, Grade
FROM student
INNER JOIN SC
USING Cno
IN (SELECT Cno FROM course WHERE Cname='数据库基础') 
AND student.Sno=SC.Sno
WHERE Ssex='男' AND Sdept='计算机系'

#15
SELECT A.Sname, A.Sage
FROM student as A
INNER JOIN student B
on A.Sage=B.Sage IN (SELECT )

SQL Tutorial
Logo