Skip to content

Commit 5640ace

Browse files
degr8sid-codedegr8sid-code
authored
Merge branch 'Baeldung:main' into main
2 parents ff06384 + 2cd34df commit 5640ace

389 files changed

Lines changed: 3041 additions & 34 deletions

File tree

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

‎data-manipulation/remove-duplicates/identifying-duplicates.sql‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
SELECT semester, year, reg_datetime, course_id, student_id, COUNT(*)
2+
FROM modifiedregistration
3+
GROUP BY semester, year, reg_datetime, course_id, student_id
4+
HAVING COUNT(*) > 1;

‎data-manipulation/remove-duplicates/removing-duplicate-row-without-unique-identifier-setup.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
CREATE TABLE modifiedregistration (
2+
semester VARCHAR(30),
3+
year INT,
4+
reg_datetime TIMESTAMP,
5+
course_id VARCHAR(10),
6+
student_id INT
7+
);
8+
9+
INSERT INTO modifiedregistration (semester, year, reg_datetime, course_id, student_id)
10+
VALUES
11+
('Fall', 2021, '2021-09-01 08:00:00', 'CS211', 1001),
12+
('Fall', 2021, '2021-09-01 08:00:00', 'CS211', 1001),
13+
('Spring', 2022, '2022-01-15 09:30:00', 'CS212', 1007),
14+
('Spring', 2022, '2022-01-15 09:30:00', 'CS212', 1007);
15+

‎data-manipulation/remove-duplicates/using-internal-ctid.sql‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
WITH duplicates AS (
2+
SELECT ctid,
3+
ROW_NUMBER() OVER(PARTITION BY semester, year, reg_datetime, course_id, student_id ORDER BY semester) AS row_num
4+
FROM modifiedregistration
5+
)
6+
DELETE FROM modifiedregistration
7+
WHERE ctid IN (SELECT ctid FROM duplicates WHERE row_num > 1);
8+
9+
select * from modifiedregistration;

‎data-manipulation/remove-duplicates/using-temporary-table.sql‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,10 @@
1+
CREATE TEMPORARY TABLE unique_rows AS
2+
SELECT DISTINCT *
3+
FROM modifiedregistration;
4+
5+
TRUNCATE TABLE modifiedregistration;
6+
7+
INSERT INTO modifiedregistration
8+
SELECT * FROM unique_rows;
9+
10+
SELECT * FROM modifiedregistration;

sql-queries-4/store-decimal-values/create-order-schema.sql renamed to data-manipulation/store-decimal-values/create-order-schema.sql

File renamed without changes.

sql-queries-4/store-decimal-values/insert-value-within-decimal-range.sql renamed to data-manipulation/store-decimal-values/insert-value-within-decimal-range.sql

File renamed without changes.

sql-queries-4/store-decimal-values/value-exceed-total-digit-integer.sql renamed to data-manipulation/store-decimal-values/value-exceed-total-digit-integer.sql

File renamed without changes.

sql-queries-4/store-decimal-values/values-exceeding-decimal-scale.sql renamed to data-manipulation/store-decimal-values/values-exceeding-decimal-scale.sql

File renamed without changes.

‎data-manipulation/window-functions-where-clause/window-functions-in-where-clause.sql‎

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,22 @@
1+
--using a Common Table Expression
2+
WITH RankedStudents AS (
3+
SELECT
4+
id, name, gpa,
5+
RANK() OVER (ORDER BY gpa DESC) AS r
6+
FROM Student
7+
WHERE gpa IS NOT NULL
8+
)
9+
SELECT id, name, gpa, r
10+
FROM RankedStudents
11+
WHERE r <= 3;
12+
13+
--using a subquery
14+
SELECT *
15+
FROM (
16+
SELECT
17+
id, name, gpa,
18+
RANK() OVER (ORDER BY gpa DESC) AS r
19+
FROM Student
20+
WHERE gpa IS NOT NULL
21+
) AS RankedStudents
22+
WHERE r <= 3;

‎deleting/Deleting-Rows-Using-LEFT-JOIN-SQL/LEFT-JOIN-basic usage.sql‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
SELECT
2+
s.id AS student_id,
3+
s.name AS student_name,
4+
r.course_id
5+
FROM student s
6+
LEFT JOIN registration r ON s.id = r.student_id;

0 commit comments

Comments
 (0)