Grijesh Chauhan's questions

No meu banco de dados MYSQL EMPRESA, tenho uma tabela: Worker:

mysql> DESC `Worker`;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| SSN    | varchar(64) | NO   |     | NULL    |       |
| name   | varchar(64) | YES  |     | NULL    |       |
| salary | int(11)     | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)  

Eu criei usando o seguinte comando do MySQL:

CREATE TABLE `Worker` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;  

A tabela com os seguintes dados:

mysql> SELECT * FROM `Worker` ORDER BY salary ASC;
+-----+---------+--------+
| SSN | name    | salary |
+-----+---------+--------+
| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |
| 2   | Rahul   |    300 |
| 4   | Harsh   |    500 |
| 7   | THREE   |   1000 |
+-----+---------+--------+
7 rows in set (0.00 sec)  

sua consulta de inserção:

INSERT INTO `Worker`
    (SSN, name, salary) 
  VALUES 
    ("1", "Grijesh", 200),
    ('2','Rahul',300),
    ('3','Sumit',250),
    ('4','Harsh',500),
    ('5','ONE','100'),
    ('6','TWO',50),
    ('7','THREE',1000);  

[PERGUNTA]

Eu quero selecionar primeiro n workers ( na lista asc ) a soma do salário é pouco mais de 500 (> 500). por exemplo:

os quatro trabalhadores seguintes são os primeiros funcionários de baixo salário e com SOMA de salário 600. (600 > 500)

| 6   | TWO     |     50 |
| 5   | ONE     |    100 |
| 1   | Grijesh |    200 |
| 3   | Sumit   |    250 |    

Eu preciso de algo como SELECT * FROMWorker WHERE SUM(salary) < 501 ASC; (sua consulta incorreta)

Pode-se supor que todos tenham salários diferentes.

_{é algo como corte de custos: remova funcionários que são relativamente sem importância}

Uma sugestão ou link para ler será útil

SUB-ÁRVORE DENTRO DE UMA ÁRVORE no MySQL

No meu MYSQL Database COMPANY, tenho uma Table: Employeeassociação recursiva, um funcionário pode ser chefe de outro funcionário. A self relationship of kind (SuperVisor (1)- SuperVisee (∞) ).

Consulta para criar tabela:

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `Name` varchar(64) DEFAULT NULL,
  `Designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) NOT NULL, 
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  
              FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Eu inseri um conjunto de tuplas (Query):

INSERT INTO Employee VALUES 
 ("1", "A", "OWNER",  "1"),  

 ("2", "B", "BOSS",   "1"), # Employees under OWNER 
 ("3", "F", "BOSS",   "1"),

 ("4", "C", "BOSS",   "2"), # Employees under B
 ("5", "H", "BOSS",   "2"), 
 ("6", "L", "WORKER", "2"), 
 ("7", "I", "BOSS",   "2"), 
 # Remaining Leaf nodes   
 ("8", "K", "WORKER", "3"), # Employee under F     

 ("9", "J", "WORKER", "7"), # Employee under I     

 ("10","G", "WORKER", "5"), # Employee under H

 ("11","D", "WORKER", "4"), # Employee under C
 ("12","E", "WORKER", "4")  

As linhas inseridas têm o seguinte Tree-Hierarchical-Relationship :

         A     <---ROOT-OWNER
        /|\             
       / A \        
      B     F 
    //| \    \          
   // |  \    K     
  / | |   \                     
 I  L H    C        
/     |   / \ 
J     G  D   E

Eu escrevi uma consulta para encontrar o relacionamento:

SELECT  SUPERVISOR.name AS SuperVisor, 
        GROUP_CONCAT(SUPERVISEE.name  ORDER BY SUPERVISEE.name ) AS SuperVisee, 
        COUNT(*)  
FROM Employee AS SUPERVISOR 
  INNER JOIN Employee SUPERVISEE ON  SUPERVISOR.SSN = SUPERVISEE.MSSN 
GROUP BY SuperVisor;

E a saída é:

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| A          | A,B,F      |        3 |
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| F          | K          |        1 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+
6 rows in set (0.00 sec)

[ PERGUNTA ]
Em vez de uma árvore hierárquica completa, preciso SUB-TREEde um ponto (seletivo), por exemplo:
Se Bo argumento de entrada for, a saída deve ser como abaixo ...

+------------+------------+----------+
| SuperVisor | SuperVisee | COUNT(*) |
+------------+------------+----------+
| B          | C,H,I,L    |        4 |
| C          | D,E        |        2 |
| H          | G          |        1 |
| I          | J          |        1 |
+------------+------------+----------+   

Por favor me ajude nisso. Se não for uma consulta, um procedimento armazenado pode ser útil.
Eu tentei, mas todos os esforços foram inúteis!

No meu banco de dados eu tenho uma tabela : Employeecom associação recursiva, um funcionário pode ser chefe de outro funcionário.

A descrição da tabela:

mysql> DESC Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | NO   | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Estado Atual da Tabela de Empregados é:

mysql> SELECT * FROM Employee;
    +-----+------+-------------+------+
    | SSN | name | designation | MSSN |
    +-----+------+-------------+------+
    | 1   | A    | OWNER       | NULL |
    | 2   | B    | BOSS        | 1    |  
    | 3   | C    | WORKER      | 2    |  
    | 4   | D    | BOSS        | 2    |  
    | 5   | E    | WORKER      | 4    |   
    | 6   | F    | WORKER      | 1    |  
    | 7   | G    | WORKER      | 4    |  
    +-----+------+-------------+------+
    7 rows in set (0.00 sec)

Segue a relação hierárquica entre as linhas da tabela:

     A
    / \
   B   F
  / \
 c   D
    / \
   G   E

Eu queria impor as seguintes restrições ao INSERT :

• Um empregado não pode mandar em si mesmo. Daí a consulta como.
  INSERT INTO Employee VALUES ("8", "H", "BOSS", "8"); Deve ser recusado.
• Novo novo OWNERpode ser inserido.

Como estou trabalhando com MYSQL versão anterior a 5.5 (não suporta sinal).
Portanto, estou usando a my_signal()como um procedimento armazenado.

Escrito assim:

CREATE PROCEDURE `my_signal`(in_errortext VARCHAR(255))
BEGIN
    SET @sql=CONCAT('UPDATE `', in_errortext, '` SET x=1');
    PREPARE my_signal_stmt FROM @sql;
    EXECUTE my_signal_stmt;
    DEALLOCATE PREPARE my_signal_stmt;
END// 

E para aplicar restrições , written a Triggercomo vim a saber disso check constraints are not yet implemented in MySQL!

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal('Error: can not insert new OWNER !');

       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal('Error: Row can not reference itself!');
      END CASE; 
  END$$   
DELIMITER ;  

Foi compilado e carregado com sucesso no banco de dados. Mas quando tentei inserir:

mysql> INSERT INTO Employee VALUES ("12", "K", "BOSS",   "12");
ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger

Aprendo aqui e aqui que

• As instruções SQL preparadas (PREPARE, EXECUTE, DEALLOCATE PREPARE) podem ser usadas em procedimentos armazenados, mas não em funções ou gatilhos armazenados. Portanto, funções armazenadas e gatilhos não podem usar SQL dinâmico (onde você constrói instruções como strings e depois as executa).

Depois de algum esforço, posso escrever outro gatilho conforme abaixo. Funcionando bem conforme minha necessidade.

mysql> CREATE
    -> TRIGGER `employee_before_insert` BEFORE INSERT
    ->     ON `Employee`
    ->     FOR EACH ROW BEGIN
    ->      IF UCASE(NEW.designation) = 'OWNER'  THEN  /*UCASE*/
    ->        UPDATE `Error: can not insert new OWNER !` set x=1; 
    ->      END IF;
    ->      IF (NEW.SSN = NEW.MSSN) THEN
    ->        UPDATE `Error: Row can not reference itself!` set x=1;
    ->      END IF;
    -> END$$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql> INSERT INTO Employee VALUES ("12", "K", 'owner',   "11");
ERROR 1146 (42S02): Table 'dumy.Error: can not insert new OWNER !'
  doesn't exist
mysql> INSERT INTO Employee VALUES ("12", "K", 'Ajay',   "12");
ERROR 1146 (42S02): Table 'dumy.Error: Row can not reference itself!' 
  doesn't exist

Mas já estou usando my_signal() em muitos procedimentos e preciso escrever muitas novas funções e gatilhos armazenados onde precisaria de my_signal()funções novamente.

Alguém pode me sugerir outra maneira de escrever my_signal () através da qual eu possa imprimir uma mensagem de erro personalizada?

Eu tentei da seguinte forma:

DELIMITADOR $$
CREATE PROCEDURE my_signal(in_errortext VARCHAR(255))
DECLARE sql varchar(512);
COMEÇAR
SET sql=CONCAT('ATUALIZAR ', in_errortext, 'CONJUNTO x=1');
ATUALIZAR sql SET x =1;
END$$

Mas inútil :( .

Por favor, me ajude nisso. Ficarei muito grato!

Não sou bom em MYSQL, especialmente @ Stored Procedures.

Se você quiser experimentar seu sistema aqui, poderá encontrar rapidamente comandos para criar esse banco de dados.

Como restringir a inserção na adição de linhas de auto-referência em uma tabela de relação recursiva (uma tabela contém pontos de chave estrangeira em si)?

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

Um empregado não pode ser o BOSSde si mesmos. Por isso

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");  
Query OK, 1 row affected (0.04 sec)

deve ser recusado por alguma restrição. Como alguém pode adicionar tal restrição?

Se possível sugira sem usar um gatilho.

No meu banco de dados eu tenho uma tabela : Employeecom associação recursiva, um funcionário pode ser chefe de outro funcionário.

A seguir está a consulta que usei para criar a tabela:

CREATE TABLE IF NOT EXISTS `Employee` (
  `SSN` varchar(64) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `designation` varchar(128) NOT NULL,
  `MSSN` varchar(64) DEFAULT NULL, 
  PRIMARY KEY (`SSN`),
  CONSTRAINT `FK_Manager_Employee`  FOREIGN KEY (`MSSN`) REFERENCES Employee(SSN)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

E eu poderia criar minha tabela com sucesso:

mysql> DESCRIBE Employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| SSN         | varchar(64)  | NO   | PRI | NULL    |       |
| name        | varchar(64)  | YES  |     | NULL    |       |
| designation | varchar(128) | NO   |     | NULL    |       |
| MSSN        | varchar(64)  | YES  | MUL | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Em seguida, inseri algumas tuplas na tabela Employee usando a seguinte consulta.

mysql> INSERT INTO Employee VALUES 
    -> ("1", "A", "OWNER",  NULL), 
    -> ("2", "B", "BOSS",   "1"),
    -> ("3", "C", "WORKER", "2"),
    -> ("4", "D", "BOSS",   "2"),
    -> ("5", "E", "WORKER", "4"),
    -> ("6", "F", "WORKER", "1"),
    -> ("7", "G", "WORKER", "4")
    -> ;
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0   

Agora tenho a seguinte relação hierárquica entre as linhas da tabela:

        A
       / \
      B   F
     / \
    C   D
       / \
      G   E

A seguir está a instrução Select para a tabela:

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |  
| 3   | C    | WORKER      | 2    |  
| 4   | D    | BOSS        | 2    |  
| 5   | E    | WORKER      | 4    |   
| 6   | F    | WORKER      | 1    |  
| 7   | G    | WORKER      | 4    |  
+-----+------+-------------+------+
7 rows in set (0.00 sec)

Agora, quero impor uma restrição como: If any employee (BOSS) deleted then new BOSS of workers under him become immediate BOSS of deleted employee (Old BOSS). por exemplo, se eu excluir D, torne- Bse BOSS de Ge E.

Para isso também escrevi um Trigger da seguinte forma:

mysql>  DELIMITER $$
mysql>        CREATE
    ->        TRIGGER `Employee_before_delete` BEFORE DELETE
    ->          ON `Employee`
    ->          FOR EACH ROW BEGIN
    ->          UPDATE Employee
    ->          SET MSSN=old.MSSN
    ->          WHERE MSSN=old.MSSN; 
    ->        END$$
Query OK, 0 rows affected (0.07 sec)

mysql>        DELIMITER ;

Mas quando realizo alguma exclusão:

mysql> DELETE FROM Employee WHERE SSN='4';
ERROR 1442 (HY000): Can't update table 'Employee' in stored function/trigger
because it is already used by statement which invoked this stored 
function/trigger.

Eu aprendothis trigger is not possible aqui porque In MySQL triggers can't manipulate the table they are assigned to.

Existe alguma outra maneira possível de fazer isso? Alguém pode me sugerir outro método? Uma sugestão seria suficiente, mas deve ser eficiente.

EDIT: É possível usar Nested Query?
Também perguntei aqui .