py0023 ValueError: not enough values to unpack

I am following "Learn Python 3 the Hard Way_ A Very Simple Introduction to the Terrifyingly Beautiful World of Computers and Code ( PDFDrive ).pdf" document to to read out basic concept of python.

At excercide 13 "ex13.py", tell how to pass variable in python script while executing from cmd.

However i am doing practie @pycharm, faced below error while excuting script.

Content in file 
from sys import argv
script, first, second, third = argv
print("The script is called:", script)
print("Your first variable is:", first)
print("Your second variable is:", second)
print("Your third variable is:", third)

Error while execution

C:\Users\******\AppData\Local\Programs\Python\Python37-32\python.exe C:/Users/******/PycharmProjects/learnPython/ex13.py
Traceback (most recent call last):
  File "C:/Users/******/PycharmProjects/learnPython/ex13.py", line 3, in <module>
    script, first, second, third = argv
ValueError: not enough values to unpack (expected 4, got 1)
Process finished with exit code 1

So conclusion is as i told i was executing script in pycharm, which was wrong approach.

Microsoft Windows [Version *********
(c) 2018 Microsoft Corporation. All rights reserved.
C:\Users\******>cd C:\Users\*****\AppData\Local\Programs\Python\Python37-32\
C:\Users\******\AppData\Local\Programs\Python\Python37-32>python.exe C:/Users/******/PycharmProjects/learnPython/ex13.py 1st 2nd 3rd
The script is called: C:/Users/******/PycharmProjects/learnPython/test.py
Your first variable is: 1st
Your second variable is: 2nd
Your third variable is: 3rd

1st, 2nd, 3rd is variable value we passed in python script during execution

SQL 17 Delete Duplicate from a table having single column

In requirement we have table having single column, no primary key and foreign key applied on table. It is easier to delete the duplicate data from table if it having primary key applied using CTE or row_number. But if in table no primary key, delete using row_number window function will work cause it will delete all the records from table.

Alternate is 

- we can create new temp table using select distinct Into 
- delete all records from old table
- reinsert distinct record from temp table

But in this case new table create and delete object not allowed to user.

SELECT *
INTO SQL17
FROM (
	SELECT 1 id
	
	UNION ALL
	
	SELECT 2
	
	UNION ALL
	
	SELECT 1
	
	UNION ALL
	
	SELECT 2
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 3
	
	UNION ALL
	
	SELECT 4
	) t

DELETE
FROM SQL17
WHERE %%physloc%% IN (
		SELECT loc
		FROM (
			SELECT %%physloc%% loc
				, *
				, row_number() OVER (
					PARTITION BY id ORDER BY id
					) rn
			FROM SQL17
			) t
		WHERE rn > 1
		)

SQL 16 Populate total number of employee working in Department

Here we have Simple basic table structure same as  of Emp dept tables available in Oracle .

Here we need to Find the total number of employees working in Department. I am adding various alternate ways of doing this task. 

Table Employee Data Structure

Id	Name	DeptId
1	A	10
2	B	10
3	C	10
4	D	10
5	E	20
6	F	20
7	G	20
8	H	30
9	I	30

Table Department Data Structure

DeptId	Name
10	Sales
20	Dev
30	Tester

Expected Output

Id	Name	DeptId	Count
1	A	10	4
2	B	10	4
3	C	10	4
4	D	10	4
5	E	20	3
6	F	20	3
7	G	20	3
8	H	30	2
9	I	30	2

Create table Query 

SELECT *
INTO SQL16
FROM (
	SELECT 1 id
		, 'A' name
		, 10 deptid
	
	UNION ALL
	
	SELECT 2
		, 'B'
		, 10
	
	UNION ALL
	
	SELECT 3
		, 'C'
		, 10
	
	UNION ALL
	
	SELECT 4
		, 'D'
		, 10
	
	UNION ALL
	
	SELECT 5
		, 'E'
		, 20
	
	UNION ALL
	
	SELECT 6
		, 'F'
		, 20
	
	UNION ALL
	
	SELECT 7
		, 'G'
		, 20
	
	UNION ALL
	
	SELECT 8
		, 'H'
		, 30
	
	UNION ALL
	
	SELECT 9
		, 'I'
		, 30
	) t

Alternate Solution 1

SELECT *
INTO SQL16_1
FROM (
	SELECT 10 id
		, 'Sale' Dname
	
	UNION ALL
	
	SELECT 20
		, 'Dev'
	
	UNION ALL
	
	SELECT 30
		, 'QA'
	) t1

Alternate Solution 2
SELECT ID
	, NAME
	, DEPTID
	, COUNT(*) OVER (
		PARTITION BY DEPTID ORDER BY DEPTID
		) AS CUMMULATIVE_SUM
FROM SQL16;

Alternate Solution 3
SELECT ID
	, NAME
	, DEPTID
	, (
		SELECT SUM(1) CUMMULATIVE_SUM
		FROM SQL16 CI
		WHERE CI.DEPTID = CO.DEPTID
		GROUP BY DEPTID
		) AS CUMMULATIVE_SUM
FROM SQL16 CO;

Alternate Solution 4
SELECT CO.ID
	, CO.NAME
	, CO.DEPTID
	, (
		CASE 
			WHEN CI.DEPTID = CO.DEPTID
				THEN SUM(1) OVER (
						PARTITION BY CO.DEPTID ORDER BY CO.DEPTID
						)
			END
		) AS CUMMULATIVE_SUM
FROM SQL16 CO
JOIN SQL16 CI ON CO.ID = CI.ID;

Alternate Solution 5
SELECT *
FROM SQL16 p14
LEFT JOIN (
	SELECT count(*) cnt
		, deptid
	FROM SQL16
	GROUP BY deptid
	) t1 ON t1.deptid = p14.deptid

SQL15 Commulative SUM of Sales

Data is given table, we have date along with the sales. We need to calculate commulative sum for all the sales

below are the various ways to perform this task

Data in Table 

Year	Sales
2019-09-01	10
2019-10-01	60
2019-11-01	20
2019-12-01	10
2020-01-01	30
2020-02-01	20
2020-03-01	80
2021-01-01	70
2021-02-01	30
2021-03-01	10

Expected Result

year	sales	Sum_sales
2019-09-01	10	10
2019-10-01	60	70
2019-11-01	20	90
2019-12-01	10	100
2020-01-01	30	130
2020-02-01	20	150
2020-03-01	80	230
2021-01-01	70	300
2021-02-01	30	330
2021-03-01	10	340

SELECT *
INTO SQL15
FROM (
	SELECT '2019-09-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2019-10-01' AS MONTH
		, 60 AS SALES
	
	UNION ALL
	
	SELECT '2019-11-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2019-12-01' AS MONTH
		, 10 AS SALES
	
	UNION ALL
	
	SELECT '2020-01-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2020-02-01' AS MONTH
		, 20 AS SALES
	
	UNION ALL
	
	SELECT '2020-03-01' AS MONTH
		, 80 AS SALES
	
	UNION ALL
	
	SELECT '2021-01-01' AS MONTH
		, 70 AS SALES
	
	UNION ALL
	
	SELECT '2021-02-01' AS MONTH
		, 30 AS SALES
	
	UNION ALL
	
	SELECT '2021-03-01' AS MONTH
		, 10 AS SALES
	) t

SELECT t1.month
	, year(t1.month)
	, t1.sales
	, sum(t2.sales)
FROM sql15 t1
JOIN sql15 t2 ON (t1.month) >= (t2.month)
GROUP BY t1.month
	, t1.sales
	, year(t1.month)
ORDER BY 1
	, 2

Releated post - SQL 14 (Click Here)

Tags- SQL Excercise, Requirement solution, Practice, Query Logic, Assignments, SQL Practice